Solved

DB2 V8 z/OS External SQL Procedure DSNHPC

Posted on 2008-10-13
9
2,494 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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