Solved

DB2 V8 z/OS External SQL Procedure DSNHPC

Posted on 2008-10-13
9
2,511 Views
Last Modified: 2012-05-05
We are running DB2 V8 on z/OS. I want to create an External SQL Procedure (SQL only; no cobol, etc).  I have written the Create Procedure statement.  The next step is to "Preprocess the Create Procedure statement by executing program DSNHPC, with the HOST(SQL) option."  I can't find JCL for this (I found JCL for DSNHPC for a Cobol procedure, but not a SQL procedure).  Can anyone share their JCL?
Thanks!  Mary
0
Comment
Question by:techofficials
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22702975
have you tried doing it through the db2 interactive ispf panels?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22703024
0
 

Author Comment

by:techofficials
ID: 22703872
Yes, I did create it thru db2 interactive ispf panels.  That will do the Create Proc, but there is another step involved, which is to Preprocess the Create Proc.  That's where the CJL for DSNHPC comes in. Thanks
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:techofficials
ID: 22703911
Yes, I did review the instructions on the IBM site.  However, the instructions say:  " Using the DB2 SQL precompiler to convert the SQL procedure source statements into a C language program " and this is the part that needs the JCL I am asking for.  Thank You
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22704099
yes
but it also provides another mean by using the developer workbench
i think it would be easier for you to use it
if not, you can search for an example in the sdsnsamp library of the db2 product or use the db2i panels to do it
0
 

Author Comment

by:techofficials
ID: 22704152
Yes, I have read that  I can do it thru the workbench, but I don't have that downloaded, so I was hoping to  submit a batch job.  Our systems programmer is out, and I can't find it in our library (although I know it shipped with V8; I was hoping someone could share their JCL).   If no one on here has the JCL and I don't come across it, I'll download the workbench.  I was avoiding that because new installs usually create their own issues (setting up everything correctly, connections, etc.).  Thanks
0
 

Author Comment

by:techofficials
ID: 22705258
I found it.  It's in DSNTIJMV:

/*********************************************************************    
/PC      EXEC PGM=DSNHPC,                                                  
/             PARM='HOST(SQL),CCSID(&ECCSID)'                              
/STEPLIB  DD  DISP=SHR,DSN=DSN!!0.SDSNEXIT                                
/         DD  DISP=SHR,DSN=DSN!!0.SDSNLOAD                                
/SYSPRINT DD  SYSOUT=*                                                    
/SYSTERM  DD  SYSOUT=*                                                    
/SYSUDUMP DD  SYSOUT=*                                                    
/SYSCIN   DD  DSN=&&DSNHSQL,DISP=(MOD,PASS),UNIT=SYSDA,                    
/             SPACE=(800,(&WSPC,&WSPC))                                    
/SYSLIB   DD  DISP=SHR,DSN=&USER..SRCLIB.DATA                              
/SYSUT1   DD  DUMMY  <-- simplified DDL to register SQL SP                
/SYSUT2   DD  DUMMY  <-- DDL to register PSM SP (V6.1 and subsequent)      
/*                                                                        
/*********************************************************************    
 Command ===>                                                  Scroll
//*******************************************************************
//* PCC: Precompile C source generated by the previous step          
//*******************************************************************
//PCC     EXEC PGM=DSNHPC,                                          
//             COND=(4,LT,PC),                                      
//             PARM='HOST(C),MAR(1,80),CCSID(&ECCSID),TWOPASS'      
//STEPLIB  DD  DISP=SHR,DSN=DSN!!0.SDSNEXIT                          
//         DD  DISP=SHR,DSN=DSN!!0.SDSNLOAD                          
//DBRMLIB  DD  DISP=SHR,DSN=&USER..DBRMLIB.DATA(&MEM)                
//SYSPRINT DD  SYSOUT=*                                              
//SYSTERM  DD  SYSOUT=*                                              
//SYSUDUMP DD  SYSOUT=*                                              
//SYSIN    DD  DSN=&&DSNHSQL,DISP=(OLD,DELETE)                      
//SYSCIN   DD  DSN=&&DSNHOUT,DISP=(MOD,PASS),UNIT=SYSDA,            
//             SPACE=(800,(&WSPC,&WSPC))                            
//SYSLIB   DD  DISP=SHR,DSN=&USER..SRCLIB.DATA                      
//SYSUT1   DD  SPACE=(800,(&WSPC,&WSPC),,,ROUND),UNIT=SYSDA          
//SYSUT2   DD  SPACE=(800,(&WSPC,&WSPC),,,ROUND),UNIT=SYSDA          
//*                                                              
//***************************************************************
//* C: Compile the output from the precompiler                  
//***************************************************************
//C       EXEC PGM=CCNDRVR,                                      
//             COND=((4,LT,PC),(4,LT,PCC)),                      
//             PARM=('MAR(1,80) NOSEQ LO RENT LOCALE("SAA")')    
//SYSLIB   DD  DISP=SHR,DSN=CEE.V!R!M!.SCEEH.H                  
//         DD  DISP=SHR,DSN=DSN!!0.SDSNC.H                      
//SYSLIN   DD  DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,        
//             SPACE=(32000,(30,30)),                            
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)              
//SYSPRINT DD  SYSOUT=*                                          
//SYSCPRT  DD  SYSOUT=*                                          
//SYSTERM  DD  DUMMY                                            
//SYSIN    DD  DSN=&&DSNHOUT,DISP=(OLD,DELETE)                  
//SYSUT1   DD  UNIT=SYSDA,DISP=(NEW,PASS),                      
//             SPACE=(32000,(30,30)),                            
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)      
//SYSUT2   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)      
//SYSUT3   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)      
//SYSUT4   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)      
//SYSUT5   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)  
//SYSUT6   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)  
//SYSUT7   DD  UNIT=SYSDA,DISP=(NEW,PASS),              
//             SPACE=(32000,(30,30)),                    
//             DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)        
//SYSUT8   DD  UNIT=SYSDA,DISP=(NEW,PASS),                    
//             SPACE=(32000,(30,30)),                        
//             DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)        
//SYSUT9   DD  UNIT=SYSDA,DISP=(NEW,PASS),                    
//             SPACE=(32000,(30,30)),                        
//             DCB=(RECFM=VB,LRECL=137,BLKSIZE=882)          
//SYSUT10  DD  SYSOUT=*                                      
//SYSUT14  DD  UNIT=SYSDA,DISP=(NEW,PASS),                    
//             SPACE=(32000,(30,30)),                        
//             DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)        
//*                                                          
//*****************************************************************
//* PLKED: Pre-linkedit the object module from the C compiler      
//*****************************************************************
//PLKED   EXEC PGM=EDCPRLK,COND=((4,LT,PC),(4,LT,PCC),(4,LT,C))    
//SYSMSGS  DD  DISP=SHR,                                          
//             DSN=CEE.V!R!M!.SCEEMSGP(EDCPMSGE)                  
//SYSLIB   DD  DUMMY                                              
//SYSIN    DD  DSN=&&LOADSET,DISP=(OLD,DELETE)                    
//SYSMOD   DD  DSN=&&PLKSET,UNIT=SYSDA,DISP=(MOD,PASS),            
//             SPACE=(32000,(30,30)),                              
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)                
//SYSDEFSD DD  DUMMY                                              
//SYSOUT   DD  SYSOUT=*                                            
//SYSPRINT DD  SYSOUT=*                                            
//*                                                                
//********************************************************************
//* LKED: Linkedit the output from the pre-linkeditor                
//********************************************************************
//LKED    EXEC PGM=IEWL,                                              
//             COND=((4,LT,PC),(4,LT,PCC),(4,LT,C),(4,LT,PLKED)),    
//             PARM='MAP'                                            
//SYSLIB   DD  DISP=SHR,DSN=CEE.V!R!M!.SCEELKED                      
//         DD  DISP=SHR,DSN=DSN!!0.SDSNLOAD                          
//SYSLIN   DD  DSN=&&PLKSET,DISP=(OLD,DELETE)                        
//         DD  DDNAME=SYSIN                                          
//SYSLMOD  DD  DISP=SHR,DSN=&USER..RUNLIB.LOAD(&MEM)                  
//SYSPRINT DD  SYSOUT=*                                              
//SYSUT1   DD  SPACE=(32000,(30,30)),UNIT=SYSDA                      
//*DSNHSQL PEND         REMOVE * FOR USE AS INSTREAM PROCEDURE        
./  ADD NAME=DSNUPROC                                                
//DSNUPROC PROC LIB='DSN!!0.SDSNLOAD',                                
//         SYSTEM=DSN,                                                
//         SIZE=0K,UID='',UTPROC=''                                  
//         SIZE=0K,UID='',UTPROC=''  
//*                                  

Thanks
0
 

Author Closing Comment

by:techofficials
ID: 31407073
Thanks. Please see my last posting for the JCL.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22708951
it's good
but you will need to do some modifications for example replace all the DSN!!0 with the prefix of your db2 libraries
again, i suggest you will also try the other alternatives to do it
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question