Generate SELECT, INSERT, UPDATE and DELETE statements on tables and views within RC/Query
Article ID: 244405
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.
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
Feedback
Yes
No
Powered by
