Solved

DB2 V8 z/OS External SQL Procedure DSNHPC

Posted on 2008-10-13
9
2,475 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS/SQL OpenQuery to linked DB2 Server error 3 102
DB2 Month between two dates 15 77
aix tls version 6 163
DB2 - LOG FILES. 4 16
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now