DSNTEP2 - How to extract data to a gdg?

goldfishhh · #1 (**permalink**) 06-21-10, 09:54

I think I'm missing a step in my JCL somewhere or a parameter in DSNTEP2.

This JCL:

Code:

//PRDABDID EXEC PGM=IKJEFT1B,DYNAMNBR=20,REGION=0M         
//STEPLIB  DD DSN=DB2DB2B.DSNLOAD.TEMP,DISP=SHR            
//         DD DSN=DB2DB2B.DSNLOAD.PERM,DISP=SHR            
//         DD DSN=DB2DB2B****NLIB.LOAD.TEMP,DISP=SHR        
//         DD DSN=DB2DB2B****NLIB.LOAD.PERM,DISP=SHR        
//SYSPRINT DD SYSOUT=*                                            
//SYSTSPRT DD SYSOUT=*                                     
//SYSTSIN  DD DSN=TOTI.LIB.SQL(DB2BT),DISP=SHR             
//SYSIN    DD DSN=TOTI.LIB.SQL(PRDABDID),DISP=SHR          
//SYSOUT   DD DSN=TOTI.DB2.I234.ID,DISP=(,CATLG,DELETE)

Produces nothing in TOTI.DB2.I234.ID. However, if I change the sysprint statement to be a dsn:

Code:

//PRDABDID EXEC PGM=IKJEFT1B,DYNAMNBR=20,REGION=0M             
//STEPLIB  DD DSN=DB2DB2B.DSNLOAD.TEMP,DISP=SHR                
//         DD DSN=DB2DB2B.DSNLOAD.PERM,DISP=SHR                
//         DD DSN=DB2DB2B****NLIB.LOAD.TEMP,DISP=SHR            
//         DD DSN=DB2DB2B****NLIB.LOAD.PERM,DISP=SHR            
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),                         
//            SPACE=(CYL,(250,100),RLSE),                      
//            UNIT=DISK,                                       
//            DCB=(RECFM=FB,LRECL=133,BLKSIZE=0),              
//            DSN=TOTI.DB2.I234.ID2                            
//SYSTSPRT DD SYSOUT=*                                         
//SYSTSIN  DD DSN=TOTI.LIB.SQL(DB2BT),DISP=SHR                 
//SYSIN    DD DSN=TOTI.LIB.SQL(PRDABDID),DISP=SHR              
//SYSOUT   DD DSN=TOTI.DB2.I234.ID,DISP=(,CATLG,DELETE)

TOTI.DB2.I234.ID is still blank, but TOTI.DB2.I234.ID2 has the SQL input and output:

Code:

1                                                 
1PAGE    1                                        
 ***INPUT STATEMENT:                              
  SELECT                                          
   MAX(PROD_ABEND_ID) + 5                         
  FROM                                            
   TOTDB01.PROD_ABEND;                            
        +----------------+                        
        |                |                        
        +----------------+                        
      1_|         193774 |                        
        +----------------+                        
0SUCCESSFUL RETRIEVAL OF          1 ROW(S)        
1PAGE    1

I've also tried to create a step called REPORT and send that to a DSN which didn't work.

I'm trying to get just the result of the query into a DSN, in this case 193774.

Stealth_DBA · #2 (**permalink**) 06-21-10, 11:17

goldfishh, There are no parameters for what you are asking for with DSNTEP2 (OR DSNTEP4). I can find out information about these programs in the Utility Guide and Reference manual which you can download from:
DB2 V9.1 - DB2 for z/OS PDF books for information center topics

PS as you found out the results are sent to SYSPRINT.

dav1mo · #3 (**permalink**) 06-22-10, 08:18

Don't you normally have to specify which GDG as in (+1)?
Dave

umayer · #4 (**permalink**) 06-23-10, 08:46

Quote:

Originally Posted by goldfishhh

I'm trying to get just the result of the query into a DSN, in this case 193774.

Consider using DSNTIAUL

// EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSREC00 DD DSN=your-file,DISP=.....
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(your-system)
RUN PROG(DSNTIAUL) LIB('your-library') PARM('SQL')
//SYSIN DD *
SELECT
CHAR( MAX(PROD_ABEND_ID) + 5 )
FROM
TOTDB01.PROD_ABEND;
//*

the result of the query ( 193774 ) will be written to your-file

goldfishhh · #5 (**permalink**) 06-23-10, 11:14

Good direction! I think I might change what I did because RTFM just didn't help too much. Heh. It's ugly, but it works.

Code:

//PRDABDID EXEC PGM=IKJEFT1B,DYNAMNBR=20,REGION=0M          
//STEPLIB  DD DSN=DB2DB2B.DSNLOAD.TEMP,DISP=SHR             
//         DD DSN=DB2DB2B.DSNLOAD.PERM,DISP=SHR             
//         DD DSN=DB2DB2B****NLIB.LOAD.TEMP,DISP=SHR         
//         DD DSN=DB2DB2B****NLIB.LOAD.PERM,DISP=SHR         
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),                      
//            SPACE=(CYL,(250,100),RLSE),                   
//            UNIT=DISK,                                    
//            DCB=(RECFM=FB,LRECL=133,BLKSIZE=0),           
//            DSN=TOTI.DB2.I234.PRT                         
//SYSTSPRT DD SYSOUT=*                                      
//SYSTSIN  DD DSN=TOTI.LIB.SQL(DB2BT),DISP=SHR              
//SYSIN    DD DSN=TOTI.LIB.SQL(PRDABDID),DISP=SHR           
//SYSOUT   DD *                                             
//GET234ID EXEC PGM=IKJEFT01,PARM='SCRAPEID'                 
//SYSPROC  DD DSN=TOTI.LIB.REXX,DISP=SHR                     
//SYSTSPRT DD SYSOUT=*                                       
//SYSTSIN  DD DUMMY                                          
//INFILE   DD DISP=SHR,DSN=TOTI.DB2.I234.PRT                 
//REPORT   DD DSN=TOTI.DB2.I234.ID,DISP=(,CATLG,DELETE),     
//            UNIT=SYSDA,SPACE=(CYL,(10,3),RLSE),            
//            DCB=(RECFM=FBA,LRECL=1000)                     
//*

The *.PRT file looks like this:

Code:

1                                            
1PAGE    1                                   
 ***INPUT STATEMENT:                         
  SELECT                                     
   MAX(PROD_ABEND_ID) + 5                    
  FROM                                       
   TOTDB01.PROD_ABEND;                       
        +----------------+                   
        |                |                   
        +----------------+                   
      1_|         193774 |                   
        +----------------+                   
0SUCCESSFUL RETRIEVAL OF          1 ROW(S)   
1PAGE    1

The REXX script then finds the number and puts it into another file all by its self. I know there is some extra stuff in the REXX script, but since OUT.1 is all I need and I needed a way to not loop to every line, an old script fit the bill just fine.

Code:

/*********  REXX  **********************************/             
                                                                  
LOOP:                                                             
  "EXECIO 1 DISKR INFILE (      STEM IN."                         
   IF RC > 0 THEN                                                 
      SIGNAL XXIT                                                 
   IN.1 = ' 'SUBSTR(IN.1,2)                                       
                                                                  
LOOP2:                                                            
    IF SUBSTR(IN.1,7,2) = '1_' THEN                               
       SAVELINE = LEFT(IN.1,49)                                   
    ELSE                                                          
   IF SUBSTR(IN.1,2,10) = 'SUCCESSFUL' THEN                       
      DO                                                          
         WORK = SUBSTR(SAVELINE,2,7)                              
           IF DATATYPE(WORK) = 'NUM' THEN                         
              ADJNUM = WORK + 100000                              
         OUT.1 = STRIP(SUBSTR(SAVELINE,16,10))                    
        "EXECIO 1 DISKW REPORT  (       STEM OUT."                

      END                             
   SIGNAL LOOP                        
                                      
XXIT:

The ID file looks like this:

Code: