• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2565
  • Last Modified:

DB2 V8 z/OS External SQL Procedure DSNHPC

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
techofficials
Asked:
techofficials
  • 5
  • 4
1 Solution
 
momi_sabagCommented:
have you tried doing it through the db2 interactive ispf panels?
0
 
momi_sabagCommented:
0
 
techofficialsAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
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
 
techofficialsAuthor Commented:
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
 
techofficialsAuthor Commented:
Thanks. Please see my last posting for the JCL.
0
 
momi_sabagCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now