Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: DSNTEP2 - How to extract data to a DSN?

    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.
    Last edited by goldfishhh; 06-21-10 at 11:19.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Don't you normally have to specify which GDG as in (+1)?
    Dave

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by goldfishhh View Post
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    54
    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:
    193774

  6. #6
    Join Date
    Nov 2004
    Posts
    54
    Quote Originally Posted by dav1mo View Post
    Don't you normally have to specify which GDG as in (+1)?
    Dave
    Well, yea. I had a typeo in my subject which the system won't let me change - it is correct in my message. Silly computers.
    Last edited by goldfishhh; 06-23-10 at 15:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •