Vladimir Mestovski
New User
Joined: 15 Nov 2011 Posts: 4 Location: Belarus
|
|
|
|
Thank you very much GuyC, the DB2_ROW_NUMBER is exactly what I need.
I'm creating a program that combines almost all features of DSNTEP4, DSNTIAUL, LOAD utility, QMF style of a SELECT report, and more...
Don't know if similar program already exists...Here is an output of the program:
*** SQLMON - Batch SQL Monitor *** (built on Nov 16 2011 02:00:05 by VM)
*** CURRENT TIMESTAMP = 2011-11-16-02.58.16.044467
*** Used parameters:
INSQL(DD:SYSIN)
TAGS()
OUTSQL()
CONNECT TO BYDB2LAB;
*** CONNECT successful, SERVER_NAME='BYDB2LAB'
WRITE CSV FILE DD:DSN1 SELECT * FROM CPEOUTT.TIM_OVG_CLASS WITH UR;
*** Successful WRITE of 134355 record(s), RECFM=FB, LRECL=194
*** CPU time = 1.3602 seconds, TOTAL time = 4 seconds
CONNECT TO BYDB2LABE;
*** CONNECT successful, SERVER_NAME='BYDB2LABE'
DECLARE GLOBAL TEMPORARY TABLE SESSION.TIM_OVG_CLASS
LIKE CPEOUT.TIM_OVG_CLASS;
*** DECLARE successful
*** CPU time = 0.0056 seconds, TOTAL time = 0 seconds
READ CSV FILE DD:DSN1 INSERT INTO SESSION.TIM_OVG_CLASS VALUES(*);
*** VALUES(1.14,16.14,31.10,42.11,54.10,65.11,77.10,88.11,100.10,111.11,123.10,
*** Successful READ & INSERT of 134355 row(s)
*** CPU time = 2.4608 seconds, TOTAL time = 5 seconds
CREATE INDEX SESSION.I1 ON SESSION.TIM_OVG_CLASS (STAGE_PN);
*** CREATE successful
*** CPU time = 0.4951 seconds, TOTAL time = 2 seconds
EXPLAIN SELECT * FROM SESSION.TIM_OVG_CLASS WHERE STAGE_PN LIKE '00%';
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
|PARENT|QUERY| QUERY| | | |USED | | | |
|QBLOCK|BLOCK| BLOCK|RUN |JOIN |ACCESS|INDEX|TABLE |TABLE| TABLE | TABLE
|NO |NO | TYPE |ORDER|METHOD|TYPE |COLS |NUMBER|TYPE | OWNER | NAME
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
| 0 | 1 |SELECT| 1 | 0 | I | 1 | 1 | T |SESSION|TIM_OVG_CLAS
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
*** Successful retrieval of 1 row(s)
*** CPU time = 0.0004 seconds, TOTAL time = 0 seconds |
|