Fixing DB2 data for A Given table using Batch Program.

Hi Mainframe,

Batch programming is a very powerful process which helps process Millions of record from a input data source. An Input data source can be a VSAM dataset, SMS files or Database Such as DB2 or IMS. 

In these Blog we will be studying the Batch Application to update DB2 database which is our source of data having bad column value in it. 

Many a times while working on Application Development or Enhancement, there is an unwanted Initialize or Move Statement in the Application program which cause the Database to be update with wrong values. And it becomes important to Fix this. As the Above Database has impacted 1000's & 10,000's of record values it becomes impossible to update each record with correct field without a Batch module.

To facilitate the Batch update, we have been provided with many a techniques to collectively select and update a output desired records.

One of those techniques which is widely and commonly used by Application developer is the use of CURSOR's.

Cursor's are a very flexible means to select and Records with desired condition in the where clause and store it in the result table which can be scrolled and fetched accordingly to one's need.

Cursor Mainly consist of a 4 Step process.

1. Declaration 2. Open 3. Fetch 4. Close

Step1. Declaration is the most important and initial step of a cursor definition which finalize what option and type of Cursor is to be used for a given Application. Cursor declaration is code in the working-storage section or procedure division. preferably working-storage section.

setting up a cursor option is the work of system programmer/developer to know what a cursor should do and behave. some of the option are 

DECLARE CURSOR_NAME ROW POSITIONED SCROLL/NO SCROLL CURSOR WITH HOLD/ WITHOUT HOLD FOR 

in these example we will be using WITH HOLD for option which means that even if any one of the cursor is closed by the Application, but the cursor which is declared with WITH HOLD will be hold from getting closed Automatically.

Syntax:

    EXEC SQL (the sql delimiter)
         DECLARE CURSOR_NAME CURSOR WITH HOLD FOR
         SELECT .
                        .
                        .
    END-EXEC.

Step2: Is to open the Declared cursor, and the command to OPEN cursor is to be coded in Procedure division. When a application issue a OPEN statement with the correct cursor name the Application passes control to DB2 which looks in the DBRM and generate a result set of all the records selected by the SQL query and the generated data is send back to the Application which is stored in a buffer area know as result table (The result table are non-persistent by nature). One more thing to be noted is the DB2 before sending the result set back to Application sets a pointer or a cursor on the first record of the result set which is to used by the Application as the Starting point before fetching any records.

Syntax:

EXEC SQL
        OPEN CURSOR_NAME
END-EXEC.

Step3: Once the result set is successfully send to the result table of the MVS region, Application waits of Any Fetch statement once the fetch statement is issued a record pointed by the pointer/cursor is written in the INTO variable, this variable storage space can be working-storage or host variables of DCLGEN. A flag set logic can be used to Fetch records 1 by 1 until an SQLCODE of +100 is encountered.

Syntax:

    EXEC SQL

            FETCH CURSOR_NAME

                INTO :working-storage variables,

                           :host variables,

                           .

                           .

    END-EXEC.

For the Above cursor Fetch I have used the DCLGEN variables

For the Above the cursor Fetch I have used the WS variables

The reason I have used a 2 cursor for this particular Application is to select a nested records on cursor 2 using the selection result of cursor1.

Step4: Close the cursor, with the close of the cursor the Result table is destroyed.

Last but the not least is the extraction of required column values from the cursor2 and do any string parsing if required and pass the value in to desired update table host variable and output structure  before issuing a update and write file followed by it.

   

THANK YOU, hope it helps you learn DB2 cursor.

(I used VS code for COBOL Application development)

Sandeep R Prajapati

Technology Analyst