WHITE PAPER

ON

A CONFLICT:

BETWEEN

END-USER DEMANDS TO MANAGE DATA, RAPIDLY AND RELIABLY

AND

THE SLOW AND BUG-RIDDEN NATURE OF THE APPLICATION DEVELOPMENT PROCESS

 

AND

A RESOLUTION:

RELATIONAL DATABASE AID (RDBA) – CICS OPTION

A TOOL BY SAKMAN SOFTWARE

TO ENABLE END-USERS TO ACCESS AND UPDATE DATA  WITHOUT THE TRADITIONAL APPLICATION DEVELOPMENT

 

IN THE ENVIRONMENT OF

IBM MAINFRAME WITH DB2 AND CICS

 

 

            This White Paper is on RELATIONAL DATABASE AID (RDBA), which is a Productivity Tool by SAKMAN SOFTWARE that enables end-users to access and update DB2 data without having to go through the traditional application development steps that are typical in a COBOL(PL/I)/DB2/CICS/BMS environment.

 

            We will describe how RDBA eliminates the most time consuming stages of a typical, traditional development process in a COBOL(PL/I)/DB2/CICS/BMS environment, and achieves the following economy:

 

 

Traditional Stages of Development Process

RDBA Stages of Development Process

   1

A request is made to CICS system programmers to define a new TRAN ID for the task.

Not needed.

   2

A MAPSET and MAPs of CICS Screens are designed, coded in BMS,  compiled with assembler, and tested.

Not needed.

   3

SQL statement(s) is/are developed for the central task of the application, and tested using TSO facilities.

The same.

          With a better facility: Everything is done in RDBA,

                                              no TSO is needed.

4

One or more CICS COBOL (or PL/I, etc.) programs, copybooks are designed, coded, and tested in order to embed SQL statements, other DB2 Commands, CICS Commands, map formatting, navigation and processing logic.

Not needed.

 5

These components are packaged together and migrated through Test Environments, and ultimately to Production Environment.

Not needed.

 6

The users are notified of the name of the TRANID to use.

The users are notified of the name of the QUERY to use.

 

Typical Duration: A Few Weeks

Typical Duration: A Few Hours

The flow of the main argument is as follows:

 

1. A CONFLICT between end-users and application development department is given about an application development project.

2. THE CAUSES OF THIS CONFLICT are examined.

3. THE PROPOSED RESOLUTION OF THIS CONFLICT, RDBA, is introduced.

4. In order to concretize the claims of RDBA, an application task is suggested under A DEVELOPMENT SCENARIO.

5. TRADITIONAL DEVELOPMENT STEPS FOR THIS TASK are summarized.

6. CORRESPONDING (REDUCED) DEVELOPMENT STEPS FOR THIS TASK THRU RDBA are presented.

7. ACTUAL RDBA SCREEN SHOTS FOR THIS DEVELOPMENT SCENARIO, depicting the utilization of RDBA during the development process and during the end-user employment of the application, are presented.

8. OTHER CAPABILITIES PROVIDED BY RDBA are presented.

 

1. A CONFLICT

 

END-USER REP: We want a CICS Screen that will show this and that from DB2 Tables; also another CICS Screen that will let us update this and that.

IT REP                : When do you want it?

END-USER REP: Yesterday! J OK, OK, tomorrow is also fine.

IT REP                : No way, this product can be completed normally in two weeks, but we can deliver it, crush, in a week.

 

A week later, the product is delivered to the End-User.

END-USER REP: It is not working the way we want it. There are several bugs, and several nuisances.

IT REP                : Let’s see…

END-USER REP: Here ….

IT REP                : Well, I had told you that we had normally needed two weeks for this product. In every crush project, several quick-and-dirty tricks are performed, some shortcuts are taken; and sometimes dirtiness outshine quickness, and some seemingly shortcuts turn out to be dead ends. OK, let’s take the product back, and re-work it.

 

Another week later the improved product is delivered. A similar cycle, with hopefully less intensity, is re-lived.

On the third week, the IT REP chats with his/her team-mates…

IT REP                : I told them two weeks in the very beginning. Now, the third week’s end is nearing, and we are still working on it. If they had not pressed us, we would have finished it in two weeks without having to resort to that first technique that failed.

 

Meanwhile, at the USER department…

END-USER REP: What is taking them so long? They had agreed to one week, now on the third week, we still have almost nothing.

 

 

2. THE CAUSES OF THIS CONFLICT

 

  1. On the side of End-users, assuming that they are competent, i.e., they know and are able to define what they ask:

They have little or no knowledge of a System Development Process.

  1. On the side of IT, assuming that they are competent, i.e., they know perfectly well how to exploit their existing software and hardware resources:

They simply did not have enough time to deliver a viable product in the timeframe desired by their end-users.

 

3. A PROPOSED RESOLUTION TO THIS CONFLICT

 

For the resolution of such a conflict, there is not much that can be done at the END-USER level, other than requesting some patience.

 

However, at the IT level, there is something that can be done: Employ simple, affordable productivity tools to eliminate most of the traditional development process stages.

 

SAKMAN SOFTWARE’s RELATIONAL DATABASE AID (RDBA) has been designed and developed to eliminate such EndUser/IT Conflicts, i.e., to deliver applications without having to go through much of the traditional stages of the development process, thereby shortening the software manufacturing duration and eliminating much of the potential defects.

 

RDBA accomplishes this through three proprietary techniques:

 

1. For the retrieval and update of data, RDBA uses information stored in DB2 Catalogues, extensively; thus, eliminating the programming necessity of knowing the attributes of data elements (type as numeric, alphanumeric, etc., length, decimal scale, etc.). It also uses a technique whereby an SQL statement can be defined and executed with variables that are passed to it by end-users. 

2. For the presentation of data as CICS Screens (Maps), RDBA employs a copyrighted technique that creates CICS MAPs dynamically, thus eliminating the necessity of laying out a CICS  MAP, compiling it, and staging it through testing environments to production, every time a data presentation layout is needed.

3. From the standpoint of application development, both CICS utilization and access to DB2 (thru SQL) normally require a host language program (written in COBOL, PL/I, etc.). By the utilization of the previous two techniques, and by its versatile screen navigation techniques, RDBA removes the necessity of Host Language Programming for both CICS and SQL Commands.

 

4. CONCRETIZATION OF RDBA’S CLAIMS THROUGH A DEVELOPMENT SCENARIO

 

TASK:

 

            1. A program is to be developed to provide the users with a list of transactions for an account that occurred on a certain cycle date.

            2. The source of the information for this list is:

2.1.             The Account Number and Cycle Date to be entered by the user as Input Parameters to the program, and

2.2.             The result of an SQL Query, which is a Join of three DB2 Tables.

3. The most of the fields on this list will be taken directly from the tables, and some fields will be the product of calculations

                 on columns.

4. The number of lines (each line representing a transaction) may be more than number of lines a CICS Screen can have; and

           the length of each line on this list is longer than a CICS Screen can take. To overcome these predicaments, two techniques need to

           be utilized:

4.1.  Proper navigation processes must be devised for: Forward-Backward, Right-Left Scrolling; jumping to the last page or first-page of the list, and to the right-most or left-most position of lines.

4.2.  The user must be given the capability of focusing on a single line, by enabling her/him to view the line vertically (data on the columns of a certain line being viewed as lines of column name and column data), rather than horizontally.

5.      The user needs the ability to locate any text on this list, by entering a “FIND text” command ; along with the

       ability to jump to a certain line number and get the list starting there, by entering a “LOCATE line#” command.

6.      The user needs the ability to capture the list as a hard copy report, by entering a “PRINT” command, which should issue a batch job to create the report.

7.      The user needs the ability to capture the result rows of the list as a flat file, by entering an “EXPORT” command, which should issue a batch job to create that flat file, along with a COBOL COPYBOOK corresponding to the layout of that flat file.

 

5. TRADITIONAL DEVELOPMENT STEPS FOR THIS TASK:

 

1. A request is made to CICS system programmers to define a new TRAN ID to be called for the application, like “TRNH”.

2. A MAPSET and MAPs of CICS Screens (a MAP for each possible Right-Left Scrolling layout) are designed, coded in BMS,

          compiled with assembler, and tested.

3. An SQL statement for the transaction list is developed for the central task of the application, and tested using TSO facilities.

4. One or more CICS COBOL (or PL/I, etc.) programs, COPYBOOKs are designed, coded, and tested. These components should:

            4.1. Embed the central SQL statement, along with additional SQL/COBOL coding to make that central SQL statement work

                          (like INCLUDE statements, OPEN cursor, FETCH rows, CLOSE cursor).

4.2. Include the CICS interaction statements to RECEIVE Account Number and Cycle Date Parameters from the end-users,

       to SEND/RECEIVE MAPs to/from them, to HANDLE errors, and other necessary CICS pseudo-conversational CICS

       commands.

4.3.Have proper logic to handle browsing forward-backward/left-right scrolling, jumps to the first-last page or to a certain

      line number.

4.4. Have calculations with proper WORKING-STORAGE work fields, with sizes and decimal position suitable for the

       nature of the operands and operations.

4.5. Have reformatting features, for editing of numeric fields, placing the columns in a tabular form, etc.

4.6.             Include sophisticated techniques to issue BATCH jobs.

5. One or more BATCH COBOL (or PL/I, etc.) programs, COPYBOOKs, JCLs are designed, coded, and tested for PRINTing a

     Report of  the Query, or for EXPORTing the result rows of the Query as a flat file along with a COBOL COPYBOOK for the flat

     file’s layout.

6. These components are packaged together and migrated through Test Environments, and ultimately to Production Environment.

7. A simple communication takes place between the developers and the end-users: “Please use CICS TRAN ID, ‘TRNH’,  in order to     

    invoke the application.”

 

In this list of tasks, Step #4 and #5 (COBOL programming) are the most difficult, time consuming, and bug-prone one; it may take from a few days to a few weeks, depending on the complexity of the task and the experience level of the programmer.

 

Step #3 (SQL statement development), as usual with any SQL task, takes from several minutes to several hours, depending on the complexity of the task and the experience level of the programmer.

 

6. CORRESPONDING (REDUCED) DEVELOPMENT STEPS FOR THIS TASK THRU RELATIONAL DATABASE AID (RDBA)

 

1.  None

2.  None. 

      3. Almost the same: An SQL statement for the transaction list is developed for the central task of the application, tested using RDBA

           facilities, and given a name like “LIST OF TRANSACTIONS BASED ON ACCT # AND CYCLEDATE”.

4.  None

5.  None

6.  None

7. Almost the same: A simple communication takes place between the developers and the end-users: “Please use the Query named

    ‘LIST OF TRANSACTIONS BASED ON ACCT # AND CYCLEDATE’ in order to invoke the application.”

 

Step #1, 2, 4, 5, and 6, which take up the overwhelming majority of the traditional development process and which may carry many bugs, are bypassed. Their functions are automatically done by RDBA, as described above under the heading of “THE PROPOSED RESOLUTION OF THIS CONFLICT” (the proprietary techniques used by RDBA).

 

Only two of the simplest steps of the traditional development process are needed in RDBA context.

 

Thus, through the utilization of RDBA, COBOL(PL/I, etc.)/DB2/CICS application development process can be reduced from a few weeks to a few hours.

 

Naturally, not all of the COBOL(PL/I, etc.)/DB2/CICS development projects fall under the domain of  RDBA; they may be very complex, requiring much more than what RDBA can provide.

 

However, much of such projects DO FALL under the domain of  RDBA.

 

7. ACTUAL RDBA SCREEN SHOTS FOR THIS DEVELOPMENT SCENARIO

           

List of Screen Shots:

 

7.1.  Screens for the utilization of RDBA during the development process:

 

7.1.1. On a blank CICS Screen, invoke RDBA, by entering TRANCODE, “RDBA”.

          This will bring a List of Queries presently defined under RDBA.

7.1.2. Enter:

Line Command, “C” (Copy), and

QUERYNAME, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE” on the line having as

QUERYNAME, “FOR A NEW QUERY: ENTER ‘C’, QUERY NAME, AND PF6”, and

          Press F6.                           

7.1.3. You will see your QUERYNAME, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE” placed

                              in the List in its alphanumerical order.

7.1.4. On the line showing your QUERY NAME, Enter Line Command, “S” (Select) and Press ENTER.

7.1.5. You will get a Blank Page, to enter your SQL Statement.

7.1.6. , 7.1.7., 7.1.8. Enter your SQL Query Lines in a similar fashion as in TSO/ISPF, using, when necessary, the following

Line Commands:

                        I: Insert a Page of Blank Lines; C: Copy Line; M: Move Line; CC: Copy a Block of Lines; MM: Move a Block

                            of Lines; A: After; B: Before; D: Delete Line; R: Repeat Line.

                   7.1.9. On the WHERE clause of your Query, enter the values of the variables to be passed, as &001 and &002,

representing Account Number and Cycle Date, respectively. Then Press F4 to save the Query.

 

Congratulations! Your application is now ready to be used.

 

7.2.  Screens for the utilization of RDBA after the end of the development process, during the end-user utilization of the application:

 

7.2.1. On a blank CICS Screen, invoke RDBA, by entering TRANCODE, “RDBA”, which will display a List of Queries.

                             On the line showing the Queryname, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE”,

                             Enter Line Command, “S” (Select) and Press ENTER.

7.2.2. This will get you into the previously created query, where, on the Command Line, you enter the command,

           “RUN 00007165670000015759 11/14/2007” which will cause the query to run for

            Account Number, 00007165670000015759, and Cycle Date, 11/14/2007.

7.2.3. You will see the first page of the list.

Line #2 of the screen shows which part of the listing is displayed.

(Here, it shows that first 15 lines of a total 26 lines and first 77 characters of a total of 793 character-length line are

  displayed.)

7.2.4. By pressing F8, you may scroll down to the next page, which shows line #16 thru #26.

7.2.5. By pressing F11, you may scroll right, which shows column (character) #78 thru #154. 

7.2.6. On any line, you may enter Line Command, “V”, to bring a vertical display of that line.

7.2.7. On the vertical display, each column heading of the regular list becomes a line.

           (Here, it shows that Heading #1 thru #15 out of a total 39 Headings become line #1 thru #15.)

7.2.8. By pressing F8, you may scroll down to the next page, which shows line #16 thru #30 (Heading #16 thru #30).

 

Screen Shots:

 

7.1.1. On a blank CICS Screen, invoke RDBA, by entering TRANCODE, “RDBA”.

          This will bring a List of Queries presently defined under RDBA.

 

RDBA                   LIST OF QUERIES                         2008/04/28 08:45

             (LINE: 1-15 OF 225  COLUMN: 1-50 OF 50)           DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  QUERYNAME                                                                   

  CHAR(50)                                                                    

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

-  FOR A NEW QUERY: ENTER "C", QUERY NAME, AND PF6                            

-  GROUP 1 DELETE                                                             

-  GROUP 1 TRANSACTION LIST                                                   

-  GROUP 1 UPDATE                                                              

-  GROUP 2 DELETE                                                             

-  GROUP 2 TRANSACTION LIST                                                   

-  GROUP 2 UPDATE                                                              

-  INSERT A CYCLE DATE                                                        

-  LIST OF CYCLE DATES                                                        

-  LIST OF DOCUMENTS                                                           

-  LIST OF OPERATORS                                                          

-  QUERY FOR CYCLE DATES                                                      

-  SELECTION CRITERIA FOR PURCHASE TRANSACTIONS                               

-  TRANSACTION DELETE                                                         

-  TRANSACTION DETAIL                                                         

                                                                              

F1=HELP  F3=EXIT  F5=FINDNEXT  F6=EXECUTE  F7=BKW  F8=FWD  F10=LEFT  F11=RIGHT

 

7.1.2. Enter:

        Line Command, “C” (Copy), and

                    QUERYNAME, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLE DATE” on the line having as

                                                 QUERYNAME, “FOR A NEW QUERY: ENTER ‘C’, QUERY NAME, AND PF6”, and

          Press F6.   

 

RDBA                   LIST OF QUERIES                         2008/04/28 08:52

             (LINE: 1-15 OF 225  COLUMN: 1-50 OF 50)           DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  QUERYNAME                                                                   

  CHAR(50)                                                                    

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

C  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE                          

-  GROUP 1 DELETE                                                             

-  GROUP 1 TRANSACTION LIST                                                   

-  GROUP 1 UPDATE                                                             

-  GROUP 2 DELETE                                                             

-  GROUP 2 TRANSACTION LIST                                                    

-  GROUP 2 UPDATE                                                             

-  INSERT A CYCLE DATE                                                        

-  LIST OF CYCLE DATES                                                         

-  LIST OF DOCUMENTS                                                          

-  LIST OF OPERATORS                                                          

-  QUERY FOR CYCLE DATES                                                       

-  SELECTION CRITERIA FOR PURCHASE TRANSACTIONS                               

-  TRANSACTION DELETE                                                         

-  TRANSACTION DETAIL                                                         

                                                                               

F1=HELP  F3=EXIT  F5=FINDNEXT  F6=EXECUTE  F7=BKW  F8=FWD  F10=LEFT  F11=RIGHT

 

7.1.3. You will see your QUERYNAME, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE” placed

           in the List in its alphanumerical order.

 

RDBA                   LIST OF QUERIES                         2008/04/28 08:56

             (LINE: 1-15 OF 226  COLUMN: 1-50 OF 50)           DB2 SYSTEM: DB2A

COMMAND ==>                                                                    

  QUERYNAME                                                                   

  CHAR(50)                                                                    

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

-  FOR A NEW QUERY: ENTER "I", QUERY NAME, AND PF6                            

-  GROUP 1 DELETE                                                             

-  GROUP 1 TRANSACTION LIST                                                   

-  GROUP 1 UPDATE                                                             

-  GROUP 2 DELETE                                                             

-  GROUP 2 TRANSACTION LIST                                                   

-  GROUP 2 UPDATE                                                              

-  INSERT A CYCLE DATE                                                        

-  LIST OF CYCLE DATES                                                        

-  LIST OF DOCUMENTS                                                           

-  LIST OF OPERATORS                                                          

-  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE                          

-  QUERY FOR CYCLE DATES                                                       

-  SELECTION CRITERIA FOR PURCHASE TRANSACTIONS                               

-  TRANSACTION DELETE                                                          

 EXECUTE COMMAND HAS BEEN PERFORMED SUCCESSFULLY.                              

F1=HELP  F3=EXIT  F5=FINDNEXT  F6=EXECUTE  F7=BKW  F8=FWD  F10=LEFT  F11=RIGHT

 

 

7.1.4. On the line showing your QUERY NAME, Enter Line Command, “S” (Select) and Press ENTER.

 

RDBA                   LIST OF QUERIES                         2008/04/28 08:56

              (LINE: 1-15 OF 226  COLUMN: 1-50 OF 50)           DB2 SYSTEM: DB2A

 COMMAND ==>                                                                   

    QUERYNAME                                                                    

   CHAR(50)                                                                    

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

 -  FOR A NEW QUERY: ENTER "I", QUERY NAME, AND PF6                            

 -  GROUP 1 DELETE                                                             

 -  GROUP 1 TRANSACTION LIST                                                   

 -  GROUP 1 UPDATE                                                             

 -  GROUP 2 DELETE                                                              

 -  GROUP 2 TRANSACTION LIST                                                   

 -  GROUP 2 UPDATE                                                             

 -  INSERT A CYCLE DATE                                                         

 -  LIST OF CYCLE DATES                                                        

 -  LIST OF DOCUMENTS                                                          

 -  LIST OF OPERATORS                                                           

 S  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE                          

 -  QUERY FOR CYCLE DATES                                                      

 -  SELECTION CRITERIA FOR PURCHASE TRANSACTIONS                                

 -  TRANSACTION DELETE                                                         

                               

 F1=HELP  F3=EXIT  F5=FINDNEXT  F6=EXECUTE  F7=BKW  F8=FWD  F10=LEFT  F11=RIGHT

 

7.1.5. You will get a Blank Page, to enter your SQL Statement.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 08:59

 QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

 COMMAND ==>                                                                    

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

    00001                                                                      

    00002                                                                      

    00003                                                                      

    00004                                                                      

    00005                                                                      

    00006                                                                       

    00007                                                                      

    00008                                                                      

    00009                                                                       

    00010                                                                      

    00011                                                                      

    00012                                                                       

    00013                                                                      

    00014                                                                      

    00015                                                                       

    00016                                                                      

    00017                                                                       

 

 F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

7.1.6. Enter your SQL Query Lines in a similar fashion as in TSO/ISPF, using, when necessary, the following

          Line Commands:

                   I: Insert a Page of Blank Lines; C: Copy Line; M: Move Line; CC: Copy a Block of Lines; MM: Move a Block

           of Lines; A: After; B: Before; D: Delete Line; R: Repeat Line.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 09:14

QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

COMMAND ==>                                                                   

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

   00001 --                                                                   

   00002 -- THIS QUERY GIVES A LIST OF TRANSACTIONS FOR AN ACCOUNT,           

   00003 -- BASED ON THE ACCOUNT NUMBER AND DATE ENTERED AS PARAMETERS.       

   00004 -- IN ORDER TO EXECUTE THIS QUERY, ENTER ON THE COMMAND LINE:        

   00005 --    RUN ACCTNO TRANDATE                                            

   00006 --    FOR EXAMPLE: RUN 00007165670000015759 11/14/2007               

   00007 --                                                                   

   00008   SELECT                                                              

   00009          ASTMT_ORG_ACCT_NBR                                          

   00010         ,AMCI_TRN_CDE                                                

   00011         ,AMCI_AMT                                                     

   00012         ,AMCI_AMT * .001  AS COMMISSION                              

   00013         ,AMCI_DESC_TXT                                               

   00014         ,AMCI_POST_DTE                                               

   00015         ,AMCI_REF_NBR                                                

   00016         ,AMCI_PURC_DTE                                               

   00017         ,ASTMT_CYC_DTE                                                

                                                                               

F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

7.1.7. Continue entering your SQL Query Lines in a similar fashion as in TSO/ISPF, using, when necessary, the following

          Line Commands:

                   I: Insert a Page of Blank Lines; C: Copy Line; M: Move Line; CC: Copy a Block of Lines; MM: Move a Block

           of Lines; A: After; B: Before; D: Delete Line; R: Repeat Line.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 09:17

QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

COMMAND ==>                                                                   

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

   00018         ,ASTMT_INS                                                   

   00019         ,ASTMT_MSG                                                   

   00020         ,ASTMT_PAGES_NBR                                              

   00021         ,ASTMT_BEG_REGB_AMT                                          

   00022         ,ASTMT_BEG_REGB_AMT * .0012 AS RESERVE1                      

   00023         ,ASTMT_BEG_REGB_AMT * .0015 AS RESERVE2                       

   00024         ,ASTMT_BEG_REGB_AMT * .0017 AS RESERVE3                      

   00025         ,ASTMT_PMT_REGB_AMT                                          

   00026         ,ASTMT_CRD_REGB_AMT                                          

   00027         ,ASTMT_CHG_REGB_AMT                                          

   00028         ,ASTMT_MIN_REGP_AMT                                          

   00029         ,ASTMT_CRD_LNE_AMT                                           

   00030         ,ASTMT_AVAL_CRD_AMT                                          

   00031         ,ASTMT_CYC_NBR                                               

   00032         ,ASTMT_BIL_DAYS_NBR                                          

   00033         ,ASTMT_PMT_DUE_DTE                                            

   00034         ,ASTMT_ACCT_TYP_CDE                                           

                                                                              

F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

7.1.8. Continue entering your SQL Query Lines in a similar fashion as in TSO/ISPF, using, when necessary, the following

          Line Commands:

                   I: Insert a Page of Blank Lines; C: Copy Line; M: Move Line; CC: Copy a Block of Lines; MM: Move a Block

           of Lines; A: After; B: Before; D: Delete Line; R: Repeat Line.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 09:17

QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

COMMAND ==>                                                                   

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

   00035         ,ASTMT_BNK_NME                                                

   00036         ,ASTMT_1_ADR_TXT                                             

   00037         ,ASTMT_CTY_TXT                                               

   00038         ,ASTMT_ST_CDE                                                

   00039         ,ASTMT_ZIP_NBR                                               

   00040         ,ASTMT_OPN_DTE                                               

   00041         ,CUSTO_EMB_NME                                               

   00042         ,CUSTO_PART1_NME                                             

   00043         ,CUSTO_PART2_NME                                             

   00044         ,CUSTO_PART3_NME                                             

   00045         ,CUSTO_PART3_NME                                              

   00046         ,CUSTO_BIRTH_DTE                                             

   00047         ,CUSTO_SSN_NBR                                               

   00048     FROM                                                              

   00049          AB7.TB00132                                                 

   00050         ,AB7.TB00131                                                 

   00051         ,AB7.TB00193                                                  

                                                                              

F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

 

7.1.9. On the WHERE clause of your Query, enter the values of the variables to be passed, as &001 and &002,

representing Account Number and Cycle Date, respectively. Then Press F4 to save the Query.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 09:18

 QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

 COMMAND ==>                                                                   

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

    00052    WHERE                                                              

    00053          CUSTO_ACCT_NBR    = '&001'                                  

    00054      AND ASTMT_CYC_DTE     = '&002'                                  

    00055      AND CUSTO_CUS_SEQ_NBR = 1                                       

    00056      AND CUSTO_BU_NBR      = ASTMT_BU_NBR                            

    00057      AND CUSTO_SYS_CDE     = ASTMT_SYS_CDE                           

    00058      AND CUSTO_ACCT_NBR    = ASTMT_ACCT_NBR                          

    00059      AND CUSTO_BU_NBR      = AMCI_BU_NBR                             

    00060      AND CUSTO_SYS_CDE     = AMCI_SYS_CDE                            

    00061      AND CUSTO_ACCT_NBR    = AMCI_ACCT_NBR                           

    00062      AND ASTMT_CYC_DTE = AMCI_CYC_DTE                                

    00063    ORDER BY                                                          

    00064          AMCI_POST_DTE                                               

    00065    WITH UR                                                            

                                                                               

                                                                               

                                                                                

 F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

 

 

 

Congratulations! Your application is now ready to be used.

7.2.1. On a blank CICS Screen, invoke RDBA, by entering TRANCODE, “RDBA”, which will display a List of Queries.

          On the line showing the Queryname, “LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE”,

          Enter Line Command, “S” (Select) and Press ENTER.

 

RDBA                   LIST OF QUERIES                         2008/04/28 08:56

              (LINE: 1-15 OF 226  COLUMN: 1-50 OF 50)           DB2 SYSTEM: DB2A

 COMMAND ==>                                                                   

    QUERYNAME                                                                   

   CHAR(50)                                                                    

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

 -  FOR A NEW QUERY: ENTER "I", QUERY NAME, AND PF6                            

 -  GROUP 1 DELETE                                                             

 -  GROUP 1 TRANSACTION LIST                                                   

 -  GROUP 1 UPDATE                                                             

 -  GROUP 2 DELETE                                                              

 -  GROUP 2 TRANSACTION LIST                                                   

 -  GROUP 2 UPDATE                                                             

 -  INSERT A CYCLE DATE                                                         

 -  LIST OF CYCLE DATES                                                        

 -  LIST OF DOCUMENTS                                                          

 -  LIST OF OPERATORS                                                           

 S  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE                          

 -  QUERY FOR CYCLE DATES                                                      

 -  SELECTION CRITERIA FOR PURCHASE TRANSACTIONS                                

 -  TRANSACTION DELETE                                                         

 

 F1=HELP  F3=EXIT  F5=FINDNEXT  F6=EXECUTE  F7=BKW  F8=FWD  F10=LEFT  F11=RIGHT

 

7.2.2. This will get you into the previously created query, where, on the Command Line, you enter the command,

          “RUN 00007165670000015759 11/14/2007” which will cause the query to run for

            Account Number, 00007165670000015759, and Cycle Date, 11/14/2007.

 

RDBA                   QUERY MAINTENANCE/DISPLAY               2008/04/28 09:27

QUERY :  LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE           DB2ID DB2A

COMMAND ==> RUN 00007165670000015759 11/14/2007                               

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

   00001 --                                                                   

   00002 -- THIS QUERY GIVES A LIST OF TRANSACTIONS FOR AN ACCOUNT,           

   00003 -- BASED ON THE ACCOUNT NUMBER AND DATE ENTERED AS PARAMETERS.       

   00004 -- IN ORDER TO EXECUTE THIS QUERY, ENTER ON THE COMMAND LINE:        

   00005 --    RUN ACCTNO TRANDATE                                            

   00006 --    FOR EXAMPLE: RUN 00007165670000015759 11/14/2007               

   00007 --                                                                    

   00008   SELECT                                                             

   00009          ASTMT_ORG_ACCT_NBR                                          

   00010         ,AMCI_TRN_CDE                                                 

   00011         ,AMCI_AMT                                                    

   00012         ,AMCI_AMT * .001  AS COMMISSION                              

   00013         ,AMCI_DESC_TXT                                                

   00014         ,AMCI_POST_DTE                                               

   00015         ,AMCI_REF_NBR                                                

   00016         ,AMCI_PURC_DTE                                                

   00017         ,ASTMT_CYC_DTE                                               

                                                                              

F1=HELP F3=BACK/SAVE F4=SAVE F5=FINDNEXT F6=EXECUTE F7=BKW F8=FWD F12=CANCEL  

 

7.2.3. You will see the first page of the list.

           Line #2 of the screen shows which part of the listing is displayed.

           (Here, it shows that first 15 lines of a total 26 lines and first 77 characters of a total of 793 character-length line are displayed.)

 

RDBA         LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE 2008/04/28 09:28

             (LINE: 1-15 OF 26  COLUMN: 1-77 OF 793)           DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  ASTMT_ORG_ACCT_NBR   AMCI_TRN_CDE AMCI_AMT    COMMISSION      AMCI_DESC_TXT 

  CHAR(20)             CHAR(3)      DEC(9,2)    DEC(12,5)       CHAR(40)      

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

- 00007165670000015759 600               129.03          .12903 ABERDEEN COMMIS

- 00007165670000015759 600                38.02          .03802 ANN MARIES HALL

- 00007165670000015759 600                37.75          .03775 FLYING J      

- 00007165670000015759 600                33.33          .03333 WAL-MART #5450

- 00007165670000015759 400                19.99          .01999 CCS*CCS MAIL OR

- 00007165670000015759 600                18.86          .01886 LANDHOPE FARMS

- 00007165670000015759 600               139.87          .13987 ABERDEEN COMMIS

- 00007165670000015759 300              1258.73         1.25873 PAYMENT   THANK

- 00007165670000015759 600                34.00          .03400 FLYING J      

- 00007165670000015759 600                53.00          .05300 LANDHOPE FARMS

- 00007165670000015759 600                13.13          .01313 BATH & BODY WOR

- 00007165670000015759 600                13.13          .01313 BATH & BODY WOR

- 00007165670000015759 600                82.69          .08269 PEEBLES #5047 

- 00007165670000015759 600                18.89          .01889 ONE STOP LIQUOR

- 00007165670000015759 600                 5.30          .00530 AAFES ABERDEEN

                                                                              

F1=HELP F3=EXIT F4=INSBLNK F5=RFIND F6=EXECUTE F7=BKW F8=FWD F10=LEFT F11=RIGHT

 

7.2.4. By pressing F8, you may scroll down to the next page, which shows line #16 thru #26

 

RDBA         LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE 2008/04/28 09:28

             (LINE: 16-26 OF 26  COLUMN: 1-77 OF 793)          DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  ASTMT_ORG_ACCT_NBR   AMCI_TRN_CDE AMCI_AMT    COMMISSION      AMCI_DESC_TXT 

  CHAR(20)             CHAR(3)      DEC(9,2)    DEC(12,5)       CHAR(40)      

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

- 00007165670000015759 600               105.24          .10524 ABERDEEN COMMIS

- 00007165670000015759 600                39.65          .03965 FLYING J      

- 00007165670000015759 600                72.79          .07279 JCPENNEY STORE

- 00007165670000015759 600                63.97          .06397 JCPENNEY STORE

- 00007165670000015759 600                25.00          .02500 VICTORIA'S SECR

- 00007165670000015759 600                18.36          .01836 WAL-MART #5450

- 00007165670000015759 600               158.98          .15898 ABERDEEN COMMIS

- 00007165670000015759 600                40.50          .04050 FLYING J      

- 00007165670000015759 600                87.75          .08775 RONNY`S GARDEN

- 00007165670000015759 600                53.00          .05300 LANDHOPE FARMS

- 00007165670000015759 600                41.60          .04160 ACME MARKETS  7

                                                                               

                                                                              

                                                                              

                                                                               

NO MORE RECORDS FORWARD                                                       

F1=HELP F3=EXIT F4=INSBLNK F5=RFIND F6=EXECUTE F7=BKW F8=FWD F10=LEFT F11=RIGHT

 

7.2.5. By pressing F11, you may scroll right, which shows column (character) #78 thru #154. 

 

RDBA         LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE 2008/04/28 09:29

             (LINE: 16-26 OF 26  COLUMN: 78-154 OF 793)        DB2 SYSTEM: DB2A

COMMAND ==>                                                                    

                            AMCI_POST_DTE AMCI_REF_NBR              AMCI_PURC_D

                            DATE          CHAR(25)                  DATE      

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

- SARYSDE ABERDEEN PG   MD  11/01/2007    24445007304038949034871   10/30/2007

-         NORTH EAST    MD  11/05/2007    24030087308000002208940   11/01/2007

- 0816    NEWARK        DE  11/05/2007    24299167308904499189628   11/03/2007

- 0816    NEWARK        DE  11/05/2007    24299167308904499190071   11/03/2007

- ET 0456 NEWARK        DE  11/05/2007    24792627308675000511931   11/03/2007

-         NORTH EAST    MD  11/05/2007    24455017306140016753500   11/02/2007

- SARYSDE ABERDEEN PG   MD  11/08/2007    24445007311048141496647   11/06/2007

-         NORTH EAST    MD  11/09/2007    24030087312000001899479   11/07/2007

- WORLD   SMYRNA        DE  11/13/2007    24071057316987192267483   11/11/2007

-         PORT DEPOSIT  MD  11/13/2007    24427337314710000966487   11/09/2007

- 817     BEAR          DE  11/13/2007    24492797315409002758513   11/10/2007

                                                                              

                                                                               

                                                                              

                                                                              

NO MORE RECORDS FORWARD                                                        

F1=HELP F3=EXIT F4=INSBLNK F5=RFIND F6=EXECUTE F7=BKW F8=FWD F10=LEFT F11=RIGHT

 

7.2.6. On any line, you may enter Line Command, “V”, to bring a vertical display of that line.

 

RDBA         LIST OF TRANSACTIONS BASED ON ACCT# AND CYCLEDATE 2008/04/28 09:28

             (LINE: 16-26 OF 26  COLUMN: 1-77 OF 793)          DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  ASTMT_ORG_ACCT_NBR   AMCI_TRN_CDE AMCI_AMT    COMMISSION      AMCI_DESC_TXT 

  CHAR(20)             CHAR(3)      DEC(9,2)    DEC(12,5)       CHAR(40)      

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

V 00007165670000015759 600               105.24          .10524 ABERDEEN COMMIS

- 00007165670000015759 600                39.65          .03965 FLYING J      

- 00007165670000015759 600                72.79          .07279 JCPENNEY STORE

- 00007165670000015759 600                63.97          .06397 JCPENNEY STORE

- 00007165670000015759 600                25.00          .02500 VICTORIA'S SECR

- 00007165670000015759 600                18.36          .01836 WAL-MART #5450

- 00007165670000015759 600               158.98          .15898 ABERDEEN COMMIS

- 00007165670000015759 600                40.50          .04050 FLYING J      

- 00007165670000015759 600                87.75          .08775 RONNY`S GARDEN

- 00007165670000015759 600                53.00          .05300 LANDHOPE FARMS

- 00007165670000015759 600                41.60          .04160 ACME MARKETS  7

                                                                              

                                                                              

                                                                               

                                                                              

NO MORE RECORDS FORWARD                                                       

F1=HELP F3=EXIT F4=INSBLNK F5=RFIND F6=EXECUTE F7=BKW F8=FWD F10=LEFT F11=RIGHT

 

7.2.7. On the vertical display, each column heading of the regular list becomes a line.

          (Here, it shows that Heading #1 thru #15 out of a total 39 Headings become line #1 thru #15.)

 

RDBARD06           VERTICAL VIEW OF ROW NUMBER: 16             2008/04/28 09:30

             (LINE: 1-15 OF 39  COLUMN: 1-77 OF 80)            DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  COLUMN NAME        LENGTH AND SCALE      VALUE                              

  CHAR(18)           CHAR(20)             CHAR(40)                            

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

  ASTMT_ORG_ACCT_NBR CHAR(20)             00007165670000015759                

  AMCI_TRN_CDE       CHAR(3)              600                                 

  AMCI_AMT           DEC(9,2)             105.24                              

  COMMISSION         DEC(12,5)            .10524                              

  AMCI_DESC_TXT      CHAR(40)             ABERDEEN COMMISSARYSDE ABERDEEN PG  

  AMCI_POST_DTE      DATE                 11/01/2007                          

  AMCI_REF_NBR       CHAR(25)             24445007304038949034871             

  AMCI_PURC_DTE      DATE                 10/30/2007                           

  ASTMT_CYC_DTE      DATE                 11/14/2007                          

  ASTMT_INS          CHAR(10)                    8                            

  ASTMT_MSG          CHAR(10)                 567                              

  ASTMT_PAGES_NBR    DEC(3,0)             2                                   

  ASTMT_BEG_REGB_AMT DEC(11,2)            1258.23                             

  RESERVE1           DEC(15,6)            1.509876                            

  RESERVE2           DEC(15,6)            1.887345                            

                                                                              

                        F3=EXIT                                               

 

7.2.8. By pressing F8, you may scroll down to the next page, which shows line #16 thru #30 (Heading #16 thru #30).

 

RDBARD06           VERTICAL VIEW OF ROW NUMBER: 16             2008/04/28 09:31

             (LINE: 16-30 OF 39  COLUMN: 1-77 OF 80)           DB2 SYSTEM: DB2A

COMMAND ==>                                                                   

  COLUMN NAME        LENGTH AND SCALE      VALUE                              

  CHAR(18)           CHAR(20)             CHAR(40)                            

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

  RESERVE3           DEC(15,6)            2.138991                            

  ASTMT_PMT_REGB_AMT DEC(11,2)            1258.73                             

  ASTMT_CRD_REGB_AMT DEC(11,2)            19.99                               

  ASTMT_CHG_REGB_AMT DEC(11,2)            1323.84                             

  ASTMT_MIN_REGP_AMT DEC(11,2)            14.00                               

  ASTMT_CRD_LNE_AMT  DEC(11,2)            17000.00                            

  ASTMT_AVAL_CRD_AMT DEC(11,2)            15696.65                            

  ASTMT_CYC_NBR      DEC(3,0)             9                                   

  ASTMT_BIL_DAYS_NBR DEC(3,0)             29                                   

  ASTMT_PMT_DUE_DTE  DATE                 12/04/2007                          

  ASTMT_ACCT_TYP_CDE CHAR(5)              00302                               

  ASTMT_BNK_NME      CHAR(40)             A.B. BANK NATIONAL ASSOC ND         

  ASTMT_1_ADR_TXT    CHAR(40)             161 ARTHUR AVE                      

  ASTMT_CTY_TXT      CHAR(40)             PORT DEPOSIT MD                     

  ASTMT_ST_CDE       CHAR(2)              MD                                  

                                                                               

                        F3=EXIT                                               

 

           

8. OTHER CAPABILITIES PROVIDED BY RDBA

 

 

            Besides the functions described through the Screen Shots, such as parameter-driven execution of an SQL Query, cursor-sensitive navigation for Backward-Forward, Left-Right Scrolling thru Function Keys F7-F8 and F10-F11, the user has the following capabilities:

 

·        “M” on Command Line followed by F8 brings the Last Page of the listing; and “M” and F7, the First Page.

·        “M” on Command Line followed by F11 brings the Right-most Columns of the Lines; and “M” and F10, the Left-most.

·        “L line#” on Command Line followed by ENTER brings the listing starting from the line# specified.

·        “F text” on Command Line followed by ENTER brings the listing starting from the position where the text has been located; and F5 after this repeats the same “FIND text” process after the current location.

·         “PRINT” on Command Line followed by ENTER causes a BATCH JOB to be issued, which prints the list that is currently displayed.

·         “EXPORT” on Command Line followed by ENTER causes a BATCH JOB to be issued, which creates a flat file from the result rows of the query (raw data behind the list that is displayed), creates a COPYBOOK showing the layout of the flat file, and prints the list that is currently displayed.  

·        “SUBMIT jobname param1 param2 ..” on Command Line followed by ENTER causes a Batch Job as defined in RDBA’s repository under that jobname to be issued, accompanied by the parameters, which Batch Job may have any functions as defined by the developers.

·        The Query List can be updated thru the following Line Commands followed by F6:

D: Deletes a Query; U: Updates a Query Name; I: Inserts a new Query; C: Copies the contents of a Query under

a different Query Name.  

·        The Queries can be grouped by departments, and users in each department can be given a Query List specific only to their department.