search cancel

RC/Query for DB2 for Z/OS : Generate SELECT, INSERT, UPDATE and DELETE statements on tables and views

book

Article ID: 244405

calendar_today

Updated On:

Products

RC/Query for DB2 for z/OS

Issue/Introduction

There may be a need for a user to generate a SELECT, UPDATE, DELETE or INSERT SQL statements in order to carry out some data update on a TABLE or via a VIEW.

Environment

Release: R20

DB2 for Z/OS

Resolution

When using the TABLE or VIEW LIST functions where records have been returned to the screen from the catalog, there are four line commands available.

SELECT, UPDATE, DELETE and INSERT.

RQTL   20.0   --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N
Table Name ===> TB%                   > Creator ===> authid1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------authid1 -          LINE 1 OF 8    >
CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS
________ TBDEPT             authid1  DBCORPC  TSDEPT         7             N/A
SELECT__ TBEMP             authid1  DBCORPC  TSEMP          8             N/A
________ TBEMP_TASK_ROLE   authid1  DBCORPC  TSPROJ         3             N/A
UPDATE__ TBPROJECT         authid1  DBCORPC  TSPROJ         8             N/A
________ TBROLE             authid1  DBCORPC  TSROLE         4             N/A
DELETE__ TBROLE4           authid1  DBCORP   TSROLE4        4               0
________ TBTASK             authid1  DBCORPC  TSTASK         7             N/A
INSERT__ TBWORK_GROUP       authid1  DBCORPC  TSDEPT         3             N/A
******************************* BOTTOM OF DATA ********************************

These commands can be used individually against a table or view.
The commands generate a statement containing all the columns from the catalog so that the SQL can be edited if required.


The commands transfer control to ISQL Online SQL Execution screen so that actions can be taken with the generated SQL.

Any changes needed can be done using the E  - Edit SQL on the ISQL screen menu.
This starts up the ISQL Editor. If ISPF edit is desired then the ISPFEDIT command will initiate the edit from the ISQL Editor.

Example SELECT statement.

The SELECT statement generated contains the columns from the table but no WHERE clause.
IQPSQL3O 20.0.04 -------   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 EMPNO,FIRST_NAME,MIDDLE_NAME,LAST_NAME,
WORK_GROUP,PHONE_NO,SEX,COLUMN_GREATER_THAN_18_CHARSSS
FROM authid1.TBEMP

******************************** BOTTOM OF DATA *******************************

Example UPDATE statement.

The UPDATE statement is generated without the SET values and the WHERE values.
The column type and size is displayed so that the user can edit the desired values into the statement.

IQPSQL3O 20.0.04 -------   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:

UPDATE authid1.TBPROJECT
   SET   PROJ_NO =  CHAR(6),
         PROJ_NAME =  CHAR(20),
         PROJ_DEPT =  CHAR(6),
         PROJ_MGR =  CHAR(6),
         PROJ_START =  DATE(4),
         PROJ_END =  DATE(4),
         PROJ_DESC =  CHAR(100),
         PROJ_SUPERIOR =  CHAR(6)
   WHERE PROJ_NO =  CHAR(6)
     AND PROJ_NAME =  CHAR(20)
     AND PROJ_DEPT =  CHAR(6)
     AND PROJ_MGR =  CHAR(6)
     AND PROJ_START =  DATE(4)
     AND PROJ_END =  DATE(4)
     AND PROJ_DESC =  CHAR(100)
     AND PROJ_SUPERIOR =  CHAR(6)

******************************** BOTTOM OF DATA *******************************

Example DELETE statement.

The DELETE statement is generated without the WHERE values.
The column type and size is displayed so that the user can edit the desired values into the statement.

IQPSQL3O 20.0.04 -------   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:

DELETE FROM authid1.TBROLE4
   WHERE ROLE_ID =  CHAR(6)
     AND ROLE_DESC =  CHAR(50)
     AND ROLE_SALARY =  DECIMAL(16)
     AND ROLE_SALARY_CODE =  CHAR(1)

******************************** BOTTOM OF DATA *******************************

Example INSERT statement.

The INSERT statement is generated without the values.
The column type and size is displayed so that the user can edit the desired values into the statement.

IQPSQL3O 20.0.04 -------   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:

INSERT INTO authid1.TBWORK_GROUP
        (WGRP_ID,WGRP_DESC,WGRP_DEPT)
VALUES  (CHAR(6),
         CHAR(50),
         CHAR(6))

******************************** BOTTOM OF DATA *******************************

After the edits have been completed the SQL can be executed with the S  - SQL Execution menu item.

 

Additional Information

General Line Commands

Use the Interactive SQL Facility