RC/Query : Generate DML to do basic DML operations like SELECT, INSERT, UPDATE and DELETE on a table or View.
Article ID: 193401
Updated On:
Products
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
Release : R20
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 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 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
Feedback
