January 9, 2012 – 4:25 pm
DIRECT YES: When we use DIRECT YES option, BMC Unload Plus utility (ADUUMAIN) reads data directly from the table space data set or image copy data set and uses a SELECT-like syntax for data selection. We can unload all data from a table-space without requiring written SELECT statements. The data can be sorted by clustering key or [...]
November 14, 2011 – 2:35 pm
- DIS or -Display : Display a status of tablespace (ie READ-WRITE / COPY-PENDING / CHECK-PENDING / READ ONLY) -DIS DATABASE(dbname) SPACENAM(tbspace) more on Display Database Stop / Start a tablespace / indexspace: -STOP DATABASE(dbname) SPACENAM(tbspace) -START DATABASE(dbname) SPACENAM(tbspace) ACCESS(FORCE) Start: publib.boulder.ibm.com/../bjncstdb999243.htm Stop: publib.boulder.ibm.com/../bjncstod999243.htm How to find a table’s Database name and tablespace name? SELECT [...]
September 20, 2010 – 2:29 pm
Functionality Operator Logical AND <condition 1> AND <condition 2> Logical OR <condition 1> OR <condition 2> Logical NOT NOT <condition> Greater than <value 1> > <value 2> Less than <value 1> < <value 2> Greater than or equal to <value 1> >= <value 2> Less than or equal to <value 1> <= <value 2> Not [...]
September 18, 2010 – 2:37 pm
SQL statements in Easytrieve should be prefixed with the word, SQL. For eg, SQL SELECT 1 FROM + SYSIBM.SYSDUMMY1 INTO :WS-INT + is used for statement continuation in the next line. Host variable datatype mapping between Easytrieve and DB2. DB2 Datatype Number of Bytes Easytrieve variable INTEGER 4 WS-INT W 4 B SMALLINT 2 WS-SMALLINT [...]
November 3, 2009 – 4:19 pm
SYSIBM.SYSSEQUENCES has a column called MAXASSIGNEDVAL, that gets assigned whenever the values for IDENTITY/SEQUENCE columns are getting generated (at least for the cache). SELECT MAXASSIGNEDVAL FROM SYSIBM.SYSSEQUENCES WHERE SEQUENCEID = (SELECT BSEQUENCEID FROM SYSIBM.SYSSEQUENCESDEP WHERE DCREATOR = ‘USER1′ AND DNAME = ‘EMP_TABLE’ AND DCOLNAME = ‘EMP_ID’);
November 3, 2009 – 4:18 pm
Declaring (Creating) DECLARE GLOBAL TEMPORARY TABLE SESSION.EMP_TABLE (EMPID INTEGER NOT NULL, EMPNAME CHAR(20)) [ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS] Even if you do not give the qualifier SESSION, the table will be created as SESSION.EMP_TABLE. Temporary tables cannot be created with our own qualifier. Inserting INSERT INTO SESSION.EMP_TABLE VALUES(1,’KARTHIK’) Fetching SELECT * [...]
November 3, 2009 – 4:12 pm
//BINDSTEP EXEC PGM=IKJEFT01 //DBRMLIB DD DSN=my.dbrm.library,DISP=SHR //STEPLIB DD DISP=SHR, // DSN=’SSID.SDSNLOAD’ //SYSTERM DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(SSID) RETRY(2) TEST(0) BIND PACKAGE (PKGNAME) – MEMBER (PGMNAME) – OWNER (USER1) – QUALIFIER (USER1) – ACTION (REPLACE) – VALIDATE (BIND) – ISOLATION (CS) – EXPLAIN (YES) – RELEASE (COMMIT) END [...]
November 3, 2009 – 4:11 pm
//STEP001 EXEC PGM=IKJEFT01 //* //STEPLIB DD DISP=SHR,DSN=SSID.SDSNLOAD //* //DISPLAY DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSABOUT DD SYSOUT=* //SYSDBOUT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(SSID) RUN PROGRAM(PGMNAME) PLAN(PLANNAME) PARM(‘ANY PARM TO THE PGM’) – LIB(‘MYPGM.LOADLIB’) END /*
November 3, 2009 – 4:08 pm
DSNTIAUL (an assembler program used to process select statements dynamically) is used to unload the tables, or to get the results of an SQL select in the unload format. No details about the query will be displayed, just the resultset will be in the SYSPRINT. Unloading the whole table. //STEP0001 EXEC PGM=IKJEFT01 //STEPLIB DD DISP=SHR, [...]
November 3, 2009 – 4:04 pm
The output of these utilities will be exactly like executing a Spufi. It will have the complete details of the query and formatted columns. If you just want the resultset (in the unload format) Please read about the DSNTIAUL utility. DSNTEP2 / DSNTEP4: These programs are used to execute the SQL queries in batch. The [...]
