Calling user programs from VISION:Builder to perform DB2 SQL Commands

Document ID : KB000031051
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

There are two cases to consider when a subprogram is invoked by VISION:Builder to perform DB2 commands, not VISION:Builder itself. 
This discusses how these are handled.

Instructions:

Case 1 - VISION:Builder application IS NOT using DB2 Files (M4OLD, M4CORDn, M4SUBFn). 

               The user program that is Called from VISION:Builder would need to do the CONNECT, OPEN/CLOSE Plan, and DISCONNECT along with any other SQL Commands.  VISION:Builder does not do any SQL Commands when there are no DB2 Files in the application.

 

Case 2 - VISION:Builder application IS using DB2 Files (M4OLD, M4CORDn, M4SUBFn). 

               Since VISION:Builder is using DB2 Files and SQL Commands, VISION:Builder will do the CONNECT, OPEN/CLOSE Plan, and DISCONNECT when using CALL Attach. With TSO and IMS Attach, the connection work is done by TSO or IMS control programs.

                 The user program that is Called from VISION:Builder cannot use any SQL Attach Commands (CONNECT, OPEN, CLOSE, DISCONNECT).  The user program can use the other SQL Commands as needed.

                 You must BIND the VISION:Builder DBRM and the user program DBRM together into one plan that will be used to run the VISION:Builder application.

                 The user program and VISION:Builder must be using the same Attach Method; TSO, CALL, IMS.

  In the test case run here we

 

  • Did a BIND of the VISION:Builder DBRM for TSO Attach and the DBRM for the DB2 Utility DSNTEP2 into a single plan named INJCR001.
  • Set up our JCL to run VISION:Builder using TSO Attach.
  • Provided JCL for the Utility DSNTEP2,  SYSPRINT, SYSIN.
  • Added a “CALL MODULE DSNTEP2” to our application.  The application is written in ASL (Advanced Syntax Language).  The CALL was conditional so it would only execute one-time.
  • Executed the VISION:Builder application and got the results we wanted.  The standard VISION:Builder reports at M4LIST, and the utility output at SYSPRINT.

 Here is our test application and a sample of the output.

 //M4RUN  EXEC PGM=IKJEFT01,REGION=4M                             

//STEPLIB  DD DSN=ISPJCR1.DEVEL2.LOADLIB,DISP=SHR                

//         DD DSN=ISPJCR1.COMLIB45.LOADLIB,DISP=SHR              

//         DD DSN=SASC.C550.LINKLIB,DISP=SHR                     

//         DD DSN=ISPJCR1.RUNLIB.LOAD,DISP=SHR                    

//         DD DSN=DB2TEST.DSNLOAD,DISP=SHR                       

//         DD DSN=DB2TEST.RUNLIB.LOAD,DISP=SHR                   

//         DD DSN=CEE.PROD.V1R5M0.SCEERUN,DISP=SHR               

//SYSTSPRT DD SYSOUT=*                                            

//SYSTSIN  DD *                                                  

DSN SYSTEM(DB2T)                                                 

RUN PROGRAM(MARKIV) PLAN(INJCR001) LIB('ISPJCR1.DEVEL2.LOADLIB') 

END                                                               

//SYSTERM  DD SYSOUT=*                                           

//SORTLIB  DD DSN=SYS1.SORTLIB,DISP=SHR                          

//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(1,1))                       

//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(1,1))                       

//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(1,1))                       

//DSNTRACE DD SYSOUT=*                                           

//SYSPRINT DD SYSOUT=*                                           

//SYSOUT   DD SYSOUT=*                                           

//SYSIN    DD *                                                  

  SELECT NAME,CREATOR,BINDDATE,ISOLATION FROM SYSDB2.SYSPLAN     

         ORDER BY NAME;                                           

  SELECT NAME,GRANTEE,GRANTOR,GRANTEETYPE,AUTHHOWGOT,            

         BINDAUTH,EXECUTEAUTH                                    

         FROM SYSDB2.SYSPLANAUTH ORDER BY NAME,GRANTEE;          

  SELECT NAME,GRANTEE,GRANTOR,GRANTEETYPE,AUTHHOWGOT,            

         CREATETABAUTH,CREATETSAUTH                              

         FROM SYSDB2.SYSDBAUTH ORDER BY NAME,GRANTEE;            

  SELECT GRANTEE,GRANTOR,TCREATOR,TTNAME,DBNAME,AUTHHOWGOT       

         FROM SYSDB2.SYSTABAUTH                                  

         ORDER BY GRANTEE,TTNAME;                                

  SELECT * FROM SYSDB2.SYSDATABASE;                              

  SELECT NAME,CREATOR,DBNAME,BPOOL,PARTITIONS,LOCKRULE,STATUS,IMPLICIT,

         NTABLES,NACTIVE                                         

         FROM SYSDB2.SYSTABLESPACE ORDER BY NAME;                

  SELECT NAME,CREATOR,TYPE,DBNAME,TSNAME,COLCOUNT,CARD           

         FROM SYSDB2.SYSTABLES WHERE DBNAME LIKE 'IN%'           

         ORDER BY NAME,CREATOR;                                  

  SELECT NAME,CREATOR,TBNAME,TBCREATOR,UNIQUERULE,COLCOUNT,      

         CLUSTERING,CLUSTERED,DBNAME,INDEXSPACE                  

         FROM SYSDB2.SYSINDEXES                                   

         ORDER BY NAME,CREATOR;                                  

  SELECT * FROM SYSDB2.SYSUSERAUTH ORDER BY GRANTEE,TIMESTAMP;   

//M4INFO   DD DUMMY                                              

//M4LIST   DD SYSOUT=*                                            

//M4LIB    DD DSN=ISPJCR1.DEVEL2.M4LIB,DISP=SHR                  

//M4REPO   DD UNIT=SYSDA,SPACE=(CYL,(1,1))                       

//M4SORT   DD UNIT=SYSDA,SPACE=(TRK,(1,1))                       

//M4INPUT  DD *                                                   

DB2RUN2 RCTEMPL   S U   S   #T      R                            

DB2RUN2 RPLISTCNTL        Y                                      

PROC1   ERTODAY                                                  

##PROC                                                            

; Main Procedure                                                 

;                                                                

LILDATE:  FIELD D  4                                             

FEEDBACK: FIELD C 12                                             

WEEKDAY:  FIELD C 80                                             

NAME:     FIELD V 22                                             

;                                                                 

  IF F.M4OLD=1                            

  CALL MODULE DSNTEP2                     

  END                                     

;                                                                

  COMBINE LASTNAME, ',' STORE T.NAME                              

  COMBINE T.NAME, FIRSTNME, MIDINIT BLANKS 1 STORE T.NAME        

;                                                                

; Convert Birth Date to a Lilian Date using Picture 'YYYY-MM-DD' 

;                                                                 

  CALL CEEDAYS USING   BRTHDATE, 'YYYY-MM-DD',                   

                     T.LILDATE,  T.FEEDBACK                      

;                                                                

; Convert Lilian Date to a Format Showing Day of the Week        

;                                                                

  CALL CEEDATE USING T.LILDATE, 'Wwwwwwwwwz, Mmm DD, YYYY',      

                     T.WEEKDAY, T.FEEDBACK                        

;                                                                

  CALL REPORT REPORT1       ;Output Report                       

##PEND                                                           

REPORT1 ERTODAY                                        S         

REPORT1 E1                  F                                    

REPORT1 R1      EMPNO                                            

REPORT1 R1     TNAME                                             

REPORT1 R1      BRTHDATE                                          

REPORT1 R1     TLILDATE                                          

REPORT1 R1     TWEEKDAY                                            0124

REPORT1 T1   Report Showing Use of LE/370 Routines CEEDAYS and CEEDATE#

REPORT1 T1   #                                                         

REPORT1 T1   Field BRTHDATE is Input to CEEDAYS Yielding Field LILDATE#

REPORT1 T1   Field LILDATE  is Input to CEEDATE Yielding Field WEEKDAY#

REPORT2 ERTODAYX                                       E         

REPORT2 E1                  F                                    

REPORT2 R1     FTODAY                                            

REPORT2 R1     FTODAYX                                           

REPORT2 R1     FISDATE                                            

REPORT2 R1     FDATE                                             

REPORT2 R1     FJULIAN                                           

REPORT2 R1     FJULANX                                           

REPORT2 R1     FLILIAN                                           

REPORT2 T1   Report Showing Use of All Date Flags#               

*************** BOTTOM OF DATA VISION:Builder Application*********

M4LIST Output

 

APR 07, 1997  14.45.00                                                          

                                         ***************************************

                                         *   SUMMARY OF GENERATED SQL STATEMENTS

                                         ***************************************

--------------------------------------------------------------------------------

FILE>SEGMENT                    SQL STATEMENT                                  

--------------------------------------------------------------------------------

M4OLD>TEMPL        STATEMENT  1                                                

           SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,BRTHDATE FROM              STA

           INF1.INTEMPL X1 ORDER BY EMPNO                                    TAB

APR 07, 1997  14.45.00                                                         

** MK4W204  TYPE 0  NUMBER OF MESSAGES PRINTED IS    0.                        

                    M4OLD    -       32 RECORDS INPUT                          

                    M4REPO   -       58 RECORDS OUTPUT                         

                    M4INPUT  -       51 RECORDS INPUT                          

                    M4LIST   -      225 RECORDS OUTPUT                         

 

** MK4ED02  TYPE 0  REQUEST-REPORT1   REPORT-1  REQUESTOR ID-                  

 

04/07/97                             Report Showing Use of LE/370 Routines CEEDA

                                     Field BRTHDATE is Input to CEEDAYS Yielding

                                     Field LILDATE  is Input to CEEDATE Yielding

--------------------------------------------------------------------------------

  EMPNO            NAME            BRTHDATE    LILDATE            WEEKDAY      

--------------------------------------------------------------------------------

  000010  HAAS, CHRISTINE I       1933-08-14  08/14/1933  Monday, Aug 14, 1933 

  000020  THOMPSON, MICHAEL L     1948-02-02  02/02/1948  Monday, Feb 02, 1948 

  000030  KWAN, SALLY A           1941-05-11  05/11/1941  Sunday, May 11, 1941 

  000050  GEYER, JOHN B           1925-09-15  09/15/1925  Tuesday, Sep 15, 1925

  000060  STERN, IRVING F         1945-07-07  07/07/1945  Saturday, Jul 07, 1945

  000070  PULASKI, EVA D          1953-05-26  05/26/1953  Tuesday, May 26, 1953

  000090  HENDERSON, EILEEN W     1941-05-15  05/15/1941  Thursday, May 15, 1941

  000100  SPENSER, THEODORE Q     1956-12-18  12/18/1956  Tuesday, Dec 18, 1956

  000110  LUCCHESI, VINCENZO G    1929-11-05  11/05/1929  Tuesday, Nov 05, 1929

  000120  O'CONNELL, SEAN         1942-10-18  10/18/1942  Sunday, Oct 18, 1942 

  000130  QUINTANA, DOLORES M     1925-09-15  09/15/1925  Tuesday, Sep 15, 1925

  000140  NICHOLLS, HEATHER A     1946-01-19  01/19/1946  Saturday, Jan 19, 1946

  000150  ADAMSON, BRUCE          1947-05-17  05/17/1947  Saturday, May 17, 1947

  000160  PIANKA, ELIZABETH R     1955-04-12  04/12/1955  Tuesday, Apr 12, 1955

  000170  YOSHIMURA, MASATOSHI J  1951-01-05  01/05/1951  Friday, Jan 05, 1951 

  000180  SCOUTTEN, MARILYN S     1949-02-21  02/21/1949  Monday, Feb 21, 1949 

  000190  WALKER, JAMES H         1952-06-25  06/25/1952  Wednesday, Jun 25, 195

  000200  BROWN, DAVID            1941-05-29  05/29/1941  Thursday, May 29, 1941

  000210  JONES, WILLIAM T        1953-02-23  02/23/1953  Monday, Feb 23, 1953 

  000220  LUTZ, JENNIFER K        1948-03-19  03/19/1948  Friday, Mar 19, 1948 

  000230  JEFFERSON, JAMES J      1935-05-30  05/30/1935  Thursday, May 30, 1935

  000240  MARINO, SALVATORE M     1954-03-31  03/31/1954  Wednesday, Mar 31, 195

  000250  SMITH, DANIEL S         1939-11-12  11/12/1939  Sunday, Nov 12, 1939 

  000260  JOHNSON, SYBIL V        1936-10-05  10/05/1936  Monday, Oct 05, 1936 

  000270  PEREZ, MARIA L          1953-05-26  05/26/1953  Tuesday, May 26, 1953

  000280  SCHNEIDER, ETHEL R      1936-03-28  03/28/1936  Saturday, Mar 28, 1936

  000290  PARKER, JOHN R          1946-07-09  07/09/1946  Tuesday, Jul 09, 1946

  000300  SMITH, PHILIP X         1936-10-27  10/27/1936  Tuesday, Oct 27, 1936

  000310  SETRIGHT, MAUDE F       1931-04-21  04/21/1931  Tuesday, Apr 21, 1931

  000320  MEHTA, RAMLAL V         1932-08-11  08/11/1932  Thursday, Aug 11, 1932

  000330  LEE, WING               1941-07-18  07/18/1941  Friday, Jul 18, 1941 

  000340  GOUNOT, JASON R         1926-05-17  05/17/1926  Monday, May 17, 1926 

** MK4FT03  TYPE 0  END OF REPORT.                                             


** MK4ED02  TYPE 0  REQUEST-REPORT2   REPORT-1  REQUESTOR ID-                  

 

04/07/1997                                      Report Showing Use of All Date F

--------------------------------------------------------------------------------

  TODAY    TODAYX    ISDATE       DATE      JULIAN  JULANX     LILIAN          

 BROWSE -- ISPJCR1S.J08361.M4RUN.M4LIST ------------- LINE 00000275 COL 001 080

 COMMAND ===>                                                  SCROLL ===> PAGE

--------------------------------------------------------------------------------

  040797  04071997  19970407  APR 07, 1997  97097   1997097  04/07/1997        

** MK4FT03  TYPE 0  END OF REPORT.                                             

 

** MK4W204  TYPE 0  NUMBER OF MESSAGES PRINTED IS    4.                        

                    M4REPO   -       57 RECORDS INPUT                          

                    M4LIST   -       49 RECORDS OUTPUT                         

** MK4W209  TYPE 0  1058880 BYTES OF MAIN STORAGE UNUSED DURING REPORTING PHASE

******************BOTTOM OF DATA on M4LIST      ********************************

 

 

FIRST PART OF OUTPUT AT SYSPRINT FROM UTILITY

 

PAGE    1                                                                      

***INPUT STATEMENT:  SELECT NAME,CREATOR,BINDDATE,ISOLATION FROM SYSDB2.SYSPLAN

                                                                                

                                                                               

                                                                               

                                             +----------------------------------

                                             |   NAME   | CREATOR  | BINDDATE |

                                             +----------------------------------

                                           1_| INDBEDGE | ISPVNL1  | 910219   |

                                           2_| INIQ57FT | ISPTMR1  | 930712   |

                                           3_| INITPL01 | ISPLAG2  | 921103   |

                                           4_| INITPL03 | ISPLAG1  | 940301   |

                                           5_| INITPL04 | ISPLAG1  | 930811   |

                                           6_| INJCR001 | ISPJCR1  | 970407   |

                                           7_| INJCR002 | ISPJCR1  | 910409   |

                                           8_| INMH01   | ISPOMH1  | 940302   |

                                           9_| INM4CALL | ISPJCR1  | 910104   |

                                          10_| INM4IMS  | ISPJCR1  | 900507   |

                                          11_| INM4TSO  | ISPJCR1  | 900919   |

                                          12_| INM5TSO  | ISPJCR1  | 900814   |

                                          13_| INQASQL1 | ISPTMR1  | 930125   |

 . . .