Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 V8 z/OS External SQL Procedure DSNHPC

Posted on 2008-10-13
9
Medium Priority
?
2,549 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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 

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 1500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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