IBM Mainframe Forum

by **jfdutcher** » Mon Apr 28, 2014 11:04 pm

I show below the simplest of several requirements to replace the use of a view with the use of a table name instead. This is the most basic as it seems to involve only one table in the view. It seems like a no-brainer I know....but sometimes the devil 'IS' in the details. I show the current view info from the "IDT" option off from ISPF....and then the table is displayed also. Since the number of columns and their characteristics is the same.....I assume all that is required in the SQL is to substitute the table name for the view name....and all will be fine. Other SQL calls using other views are more involved and include multiple tables.....but I hope in this simple case....that it's that dead simple ???

Select all

******************************************************************
* DCLGEN TABLE(DB2TST6.V401ATXP_CUR) *
* LIBRARY(DB2.TST6.DCLGEN(V401ATXP)) *
* LANGUAGE(COBOL) *
* QUOTE *
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS *
******************************************************************
EXEC SQL DECLARE V401ATXP_CUR TABLE <-------- the exisitng 'view'
( HP_ID CHAR(9) NOT NULL,
TXP_ID_TYP_CODE CHAR(1) NOT NULL,
HP_TXP_ID CHAR(9) NOT NULL,
HP_TXP_EFF_DATE DATE NOT NULL,
HP_TXP_CNL_DATE DATE NOT NULL,
TXP_ID_SP_DOC_CODE CHAR(3) NOT NULL,
HP_BUS_NAME CHAR(53) NOT NULL,
HP_INC_TYP_CODE CHAR(5) NOT NULL,
HP_TXP_VFY_DATE DATE NOT NULL,
NAME_TYP_CODE CHAR(1) NOT NULL,
ANW_LGN_ID CHAR(8) NOT NULL,
ANW_TSP_GRP TIMESTAMP NOT NULL
) END-EXEC. Number of
Name Schema C DB Name TS Name Cols Tables
V401ATXP_CUR DB2PROD N DB2401A TS401A07 12 1

Name Schema T DB Name TS Name Cols Rows
T401ATXP DB2PROD T DB2401A TS401A07 12 1700343 <---- the table name I assume I can substitute for the view name in the SQL

Column Name Col No Col Type Length Scale Null Def FP Col Card
* * * * * * * * *
------------------ ------ -------- ------ ------ ---- --- -- -----------
HP_ID 1 CHAR 9 0 N N N 352646
TXP_ID_TYP_CODE 2 CHAR 1 0 N N N 4
HP_TXP_ID 3 CHAR 9 0 N N N 279633
HP_TXP_EFF_DATE 4 DATE 4 0 N Y N 12160
HP_TXP_CNL_DATE 5 DATE 4 0 N Y N 5312
TXP_ID_SP_DOC_CODE 6 CHAR 3 0 N N N 15
HP_BUS_NAME 7 CHAR 53 0 N N N 366566
HP_INC_TYP_CODE 8 CHAR 5 0 N N N 11
HP_TXP_VFY_DATE 9 DATE 4 0 N N N 5696
NAME_TYP_CODE 10 CHAR 1 0 N N N 3
ANW_LGN_ID 11 CHAR 8 0 N N N 348
ANW_TSP_GRP 12 TIMESTMP 10 6 N Y N 1699582

IBM Mainframe Forum

Replace DB2 "views" with "actual tables"

Replace DB2 "views" with "actual tables"