RC/Query: Generate DML to do basic DML operations like SELECT, INSERT, UPDATE and DELETE on a table or View.

book

Article ID: 193401

calendar_today

Updated On:

Products

CA RC/Query for DB2 for z/OS

Issue/Introduction

How to quickly generate DML to do basic operations like SELECT, INSERT, UPDATE and DELETE on a table or View.

Environment

DB2 for Z/OS

Resolution

RC/Query has line commands available to generate DML when TABLES or VIEWS are listed.

These four line commands, SELECT, INSERT, UPDATE and DELETE are able to generate DML and jump to ISQL(Interactive SQL) to execute it via CA Batch processor after some further edits to add final details. These commands generate the column list and the value list or where clause column list for you. Where a table contains a large number of columns this would be a very effective method to generate the DML statement. 

In ISQL the SQL can be edited either with the ISQL SQL Editor or with ISPF using the ISPFEDIT command accessed at the ISQL SQL Editor screen. The SQL can then be executed online or in batch.

Interactive SQL is available to all users from the Value Pack menu on the main CA Database Management for Db2 for Z/os menu.

Consider this simple table:

   CREATE TABLE AUTHID1.TBROLE

       (ROLE_ID CHARACTER(6) FOR SBCS DATA             NOT NULL

       ,ROLE_DESC CHARACTER(50) FOR SBCS DATA          NOT NULL

       ,ROLE_SALARY DECIMAL(16, 0)                     NOT NULL WITH DEFAULT

       ,ROLE_SALARY_CODE CHARACTER(1) FOR SBCS DATA    NOT NULL WITH DEFAULT 'A'

       ,CONSTRAINT ROLE_ID PRIMARY KEY

       (ROLE_ID

       )

       )

       IN DBCORP.TSROLE

  APPEND NO

  NOT VOLATILE CARDINALITY

  DATA CAPTURE NONE

  AUDIT NONE

  CCSID EBCDIC;

=====================================================================================

Let’s start by INSERTing a RECORD

List the table and enter the INSERT line command.

=====================================================================================

RQTL   20.0   --------------- RC/Q Table List --------------- YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N

 Table Name ===> TBROLE                > Creator ===> AUTHID1                >

  Qualifier ===> *                     > N/A     ===> *                      >

Loc: LOCAL ---------- SSID: SSID ----------AUTHID1 -          LINE 1 OF 1    >

CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS

INSERT__ TBROLE             AUTHID1  DBCORP   TSROLE         4             N/A

 

The INSERT line command is entered into the CMD column next to the table that is the object of the work.

COMMAND       INSERT

The INSERT line command can be issued against a table or view. When issued, RC/Query will generate the necessary SQL to issue an insert statement for the object. RC/Query will call the ISQL SQL processor which will allow the SQL to be edited and execute the generated sql.

SYNTAX        INSERT

 

IQPSQL3O 20.0   -------   ISQL Online SQL Execution ------ YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

Option    ===> *                          Mode         ===> O ONLINE

DB2 SSID  ===> SSID                       DB2 Version  ===> 121M500

----------------------------------------------------------------------- AUTHID1

 

OPTIONS:                              CONTROL PARAMETERS:

 S  - SQL Execution                    Select Row Limit       ===>

 D  - Dataset I/O                      Max Character Size     ===>

 BP - Batch Processor/Submit           Commit or Rollback     ===> C   (C or R)

 E  - Edit SQL                         Continue if Warnings   ===> N   (Y or N)

                                       Continue if SQL Errors ===> N   (Y or N)

                                       Output to Dataset      ===> Y   (Y or N)

                                       View Audit File        ===> Y   (Y or N)

SQL TO BE EXECUTED:

 

INSERT INTO AUTHID1.TBROLE

        (ROLE_ID,ROLE_DESC,ROLE_SALARY,

ROLE_SALARY_CODE)

VALUES  (CHAR(6),

         CHAR(50),

         DECIMAL(16),

         CHAR(1))

 

The SQL above obviously requires further editing on the VALUES clause but the SQL has been helpfully generated with the column definition so that the user can substitute these definitions with the required values as below.

This is the edit view when the E  - Edit SQL menu item above is used to manually replace the column definition with a real value.

For example:

___

___ INSERT INTO AUTHID1.TBROLE

___         (ROLE_ID,ROLE_DESC,ROLE_SALARY,

___ ROLE_SALARY_CODE)

___ VALUES  ('999999',

___          'THIS IS A DESCRIPTION',

___          10.5,

___          'D')

 

At this point a PF3 to return to the ISQL menu is entered and then the SQL can be executed with the S  - SQL Execution  menu item. Make sure that the Commit or Rollback     ===> C is set to “C” for Commit otherwise the default is to Rollback the update.

 

The Audit report shows the result….

 INSERT INTO AUTHID1.TBROLE

        (ROLE_ID,ROLE_DESC,ROLE_SALARY,

ROLE_SALARY_CODE)

VALUES  ('999999',

         'THIS IS A DESCRIPTION',

         10.5,

         'D')

  ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 COMMIT WORK ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 =======================================================================================

Once the new record has been created the record can be updated with the UPDATE command.

List the table and enter the UPDATE line command.

=======================================================================================

 

RQTL   20.0   --------------- RC/Q Table List --------------- YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 

 DB2 Object ===> T                       Option  ===> L    Where => N

 Table Name ===> TBROLE                > Creator ===> AUTHID1                >

  Qualifier ===> *                     > N/A     ===> *                      >

Loc: LOCAL ---------- SSID: SSID ----------AUTHID1 -          LINE 1 OF 1    >

CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS

UPDATE__ TBROLE             AUTHID1  DBCORP   TSROLE         4             N/A

 

COMMAND       UPDATE

The UPDATE line command can be issued against a table or view.  When issued, RC/Query will generate the necessary SQL to issue an update statement for the object. RC/Query will then call the ISQL SQL processor which will allow the user to edit and execute the generated SQL.

SYNTAX        UPDATE

 

IQPSQL3O 20.0   -------   ISQL Online SQL Execution ------ YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

Option    ===> *                          Mode         ===> O ONLINE

DB2 SSID  ===> SSID                       DB2 Version  ===> 121M500

----------------------------------------------------------------------- AUTHID1

OPTIONS:                              CONTROL PARAMETERS:

 S  - SQL Execution                    Select Row Limit       ===>

 D  - Dataset I/O                      Max Character Size     ===>

 BP - Batch Processor/Submit           Commit or Rollback     ===> C   (C or R)

 E  - Edit SQL                         Continue if Warnings   ===> N   (Y or N)

                                       Continue if SQL Errors ===> N   (Y or N)

                                       Output to Dataset      ===> Y   (Y or N)

                                       View Audit File        ===> Y   (Y or N)

SQL TO BE EXECUTED:

 

UPDATE AUTHID1.TBROLE

   SET   ROLE_ID =  CHAR(6),

         ROLE_DESC =  CHAR(50),

         ROLE_SALARY =  DECIMAL(16),

         ROLE_SALARY_CODE =  CHAR(1)

   WHERE ROLE_ID =  CHAR(6)

     AND ROLE_DESC =  CHAR(50)

     AND ROLE_SALARY =  DECIMAL(16)

     AND ROLE_SALARY_CODE =  CHAR(1)

 

The SQL above obviously requires further editing on the SET and WHERE clause but the SQL has been helpfully generated with the column definition so that the user can substitute these definitions with the required values.

This is the edit view when the E  - Edit SQL menu item above is used to manually replace the column definition with a real value.

For example:

___ UPDATE AUTHID1.TBROLE

___    SET   ROLE_DESC =  'THIS IS A LONGER DESCRIPTION',

___          ROLE_SALARY =  50.50,

___          ROLE_SALARY_CODE =  'C'

___    WHERE ROLE_ID =  '999999'

___

At this point a PF3 to return to the ISQL menu is entered and then the SQL can be executed with the S  - SQL Execution  menu item. Make sure that the Commit or Rollback     ===> C is set to “C” for Commit otherwise the default is to Rollback the update.

 

The Audit report shows the result….

 

UPDATE AUTHID1.TBROLE

   SET   ROLE_DESC =  'THIS IS A LONGER DESCRIPTION',

         ROLE_SALARY =  50.50,

         ROLE_SALARY_CODE =  'C'

   WHERE ROLE_ID =  '999999'

 

 ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 COMMIT WORK ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

====================================================================================

The record can be selected with the SELECT command.

List the table and enter the SELECT line command.

====================================================================================

 

RQTL   20.0   --------------- RC/Q Table List --------------- YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 

 DB2 Object ===> T                       Option  ===> L    Where => N

 Table Name ===> TBROLE                > Creator ===> AUTHID1                >

  Qualifier ===> *                     > N/A     ===> *                      >

Loc: LOCAL ---------- SSID: SSID ----------AUTHID1 -          LINE 1 OF 1    >

CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS

SELECT__ TBROLE             AUTHID1  DBCORP   TSROLE         4             N/A

 

SELECT        - Jump to ISQL to select from a table or view

COMMAND       SELECT

The SELECT line command can be issued against a table or view.  When issued, RC/Query will generate the necessary SQL to issue a select statement from the object. RC/Query will the call the ISQL SQL processor which will allow the user to edit and execute the generated SQL.

SYNTAX        SELECT

 

IQPSQL3O 20.0   -------   ISQL Online SQL Execution ------ YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 

Option    ===> *                          Mode         ===> O ONLINE

DB2 SSID  ===> SSID                       DB2 Version  ===> 121M500

----------------------------------------------------------------------- AUTHID1

 

OPTIONS:                              CONTROL PARAMETERS:

 S  - SQL Execution                    Select Row Limit       ===>

 D  - Dataset I/O                      Max Character Size     ===>

 BP - Batch Processor/Submit           Commit or Rollback     ===> R   (C or R)

 E  - Edit SQL                         Continue if Warnings   ===> N   (Y or N)

                                       Continue if SQL Errors ===> N   (Y or N)

                                       Output to Dataset      ===> Y   (Y or N)

                                       View Audit File        ===> Y   (Y or N)

SQL TO BE EXECUTED:

 

SELECT ROLE_ID,ROLE_DESC,ROLE_SALARY,

ROLE_SALARY_CODE

FROM AUTHID1.TBROLE

 

The SQL above requires further editing to add a WHERE clause.

This is the edit view when the E  - Edit SQL menu item above is used to manually add a WHERE clause if required.

For example:

___

___ SELECT ROLE_ID,ROLE_DESC,ROLE_SALARY,

___ ROLE_SALARY_CODE

___ FROM AUTHID1.TBROLE

___ WHERE ROLE_ID =  '999999'

 

At this point a PF3 to return to the ISQL menu is entered and then the SQL can be executed with the S  - SQL Execution  menu item.

 

The result returned is:

--------------- RC/SQL - Browse Select Results -------------- YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

  1 ROWS RETRIEVED

ROLE_ID ROLE_DESC                                                ROLE_SALARY ROLE_SALARY_CODE

999999  THIS IS A LONGER DESCRIPTION                                      50 C

 

 

The Audit report shows the result….

 

SELECT ROLE_ID,ROLE_DESC,ROLE_SALARY,

ROLE_SALARY_CODE

FROM AUTHID1.TBROLE

WHERE ROLE_ID =  '999999'

 ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 ROLLBACK WORK ;

 

=======================================================================================

Then the record can be deleted using the DELETE command.

List the table and enter the DELETE line command.

=======================================================================================

RQTL   20.0   --------------- RC/Q Table List --------------- YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 

 DB2 Object ===> T                       Option  ===> L    Where => N

 Table Name ===> TBROLE                > Creator ===> AUTHID1                >

  Qualifier ===> *                     > N/A     ===> *                      >

Loc: LOCAL ---------- SSID: SSID ----------AUTHID1 -          LINE 1 OF 1    >

CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS

DELETE__ TBROLE             AUTHID1  DBCORP   TSROLE         4             N/A

 

 

DELETE        - Jump to ISQL to delete from a table or view

COMMAND       DELETE

The DELETE line command can be issued against a table or view.  When issued, RC/Query will generate the necessary SQL to issue  a delete statement for the object. RC/Query will the call the ISQL SQL processor which will allow the user to edit and execute the generated SQL.

SYNTAX        DELETE

 

IQPSQL3O 20.0   -------   ISQL Online SQL Execution ------ YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

Option    ===> *                          Mode         ===> O ONLINE

DB2 SSID  ===> SSID                       DB2 Version  ===> 121M500

----------------------------------------------------------------------- AUTHID1

OPTIONS:                              CONTROL PARAMETERS:

 S  - SQL Execution                    Select Row Limit       ===>

 D  - Dataset I/O                      Max Character Size     ===>

 BP - Batch Processor/Submit           Commit or Rollback     ===> C   (C or R)

 E  - Edit SQL                         Continue if Warnings   ===> N   (Y or N)

                                       Continue if SQL Errors ===> N   (Y or N)

                                       Output to Dataset      ===> Y   (Y or N)

                                       View Audit File        ===> Y   (Y or N)

SQL TO BE EXECUTED:

 

DELETE FROM AUTHID1.TBROLE

   WHERE ROLE_ID =  CHAR(6)

     AND ROLE_DESC =  CHAR(50)

     AND ROLE_SALARY =  DECIMAL(16)

     AND ROLE_SALARY_CODE =  CHAR(1)

 

The SQL above obviously requires further editing on the WHERE clause but the SQL has been helpfully generated with the column definition so that the user can substitute these values with the required values using the edit function.

For example:

___

___ DELETE FROM AUTHID1.TBROLE

___    WHERE ROLE_ID =  '999999'

___

At this point a PF3 to return to the ISQL menu is entered and then the SQL can be executed with the S  - SQL Execution  menu item. Make sure that the Commit or Rollback     ===> C is set to “C” for Commit otherwise the default is to Rollback the update.

IQPSQL3O 20.0   -------   ISQL Online SQL Execution ------ YYYY/MM/DD HH:MM

COMMAND ===>                                                  SCROLL ===> CSR

 

Option    ===> *                          Mode         ===> O ONLINE

DB2 SSID  ===> SSID                       DB2 Version  ===> 121M500

----------------------------------------------------------------------- AUTHID1

 

OPTIONS:                              CONTROL PARAMETERS:

 S  - SQL Execution                    Select Row Limit       ===>

 D  - Dataset I/O                      Max Character Size     ===>

 BP - Batch Processor/Submit           Commit or Rollback     ===> C   (C or R)

 E  - Edit SQL                         Continue if Warnings   ===> N   (Y or N)

                                       Continue if SQL Errors ===> N   (Y or N)

                                       Output to Dataset      ===> Y   (Y or N)

                                       View Audit File        ===> Y   (Y or N)

SQL TO BE EXECUTED:

 

DELETE FROM AUTHID1.TBROLE

   WHERE ROLE_ID =  '999999'

 

The Audit report shows the result….

 

DELETE FROM AUTHID1.TBROLE

   WHERE ROLE_ID =  '999999'

 

 ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 COMMIT WORK ;

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION

 

Additional Information

RC/Query:Using Line Commands

Use the Interactive SQL Facility