row_number

cberthu

Hi all,

I have written a nice application which is working nicely on a DB2
V9.1 LUW with the olap row_number, so far so good but now I have to
let this run on a mainframe (z/os) DB2 ver 8... and this does not
exist there..... the wonderfull row_number.

How can have the same result I mean I am using the row_number as
primary key in the target table...

The staement looks like that (part of REXX):

**********************************************
SELECT
WHEN TABLE == "TCEODATADSBD" THEN
DO
SQLCMD = "SELECT COUNT(*)
",
"FROM
"SCHEM2".DS_BD"
CALL CURSOR
"CLEAN_SINGLE_"TABLE"_PROC"
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"
DO UNTIL(SQLCA.SQLCODE <>
0)
IF (SQLCA.SQLCODE <'0' ) & (SQLCA.SQLCODE <>
'100')
CALL SQLCA "INPUT_SINGLE_"TABLE"_PROC: SQLEXEC
FETCH"
NUMBER = STRIP(OUTSQLDA.
1.SQLDATA)
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"

END
CALL CLOSING
"INPUT_SINGLE__"TABLE"_PROC"

IF NUMBER DIV
THEN
CALL LOADNUM
NUMBER
ELSE
DO
LOAD.NUMS =
0
TAR =
0
LOAD.NUMS = LOAD.NUMS" "TAR"
"NUMBER

END
DO H = 1 TO TAR
+1

PARSE VAR LOAD.NUMS NR1
LOAD.NUMS
IF NR1 == 0 THEN NR1 = NR1 +
1
NR2 = NR1 + (DIV
-1)
IF NR2 = DIV THEN NR2 = DIV -
1
IF NR2 NUMBER THEN NR2 =
NUMBER
IF NR2 = NUMBER THEN NR2 =
NUMBER
IF NUMBER = DIV THEN NR2 =
NUMBER
STATEMENT = "INSERT INTO "SCHEM1"."TABLE"
",
"SELECT * FROM (SELECT DSBD_NAME, DSBD_UACC,
",
"DSBD_INSTALL_DATA, ROW_NUMBER() OVER() AS R FROM
",
SCHEM2".DS_BD) AS NU WHERE R BETWEEN
",
NR1 " AND "
NR2
SAY
STATEMENT
CALL INSI STATEMENT
END /* DO H */
END /*WHEN */


Sorry about the upercase, Z7OS like.....
Rgds Chris

Jul 11 '07 #1

Lennart

cb*****@gmail.com wrote:

Hi all,

I have written a nice application which is working nicely on a DB2
V9.1 LUW with the olap row_number, so far so good but now I have to
let this run on a mainframe (z/os) DB2 ver 8... and this does not
exist there..... the wonderfull row_number.

How can have the same result I mean I am using the row_number as
primary key in the target table...

I dont understand why you do it the way you do, but that is beside the
scope of my post. Does z/os ver 8 have sequence's? If so you could
create a sequence say S:

create sequence S as integer;

and then use S as a generator:

alter sequence S restart with 1;
insert into T2
select c1, c2, ..., (nextval for S) as R
from T1;

/Lennart

[...]

Jul 12 '07 #2

Jeroen van den Broek

On Jul 12, 8:31 am, Lennart <erik.lennart.jons...@gmail.comwrote:

cber...@gmail.com wrote:

Hi all,

I have written a nice application which is working nicely on a DB2
V9.1 LUW with the olap row_number, so far so good but now I have to
let this run on a mainframe (z/os) DB2 ver 8... and this does not
exist there..... the wonderfull row_number.

How can have the same result I mean I am using the row_number as
primary key in the target table...

I dont understand why you do it the way you do, but that is beside the
scope of my post. Does z/os ver 8 have sequence's? If so you could
create a sequence say S:

create sequence S as integer;

and then use S as a generator:

alter sequence S restart with 1;
insert into T2
select c1, c2, ..., (nextval for S) as R
from T1;

/Lennart

Yes DB2v8 for zOS has sequences.
Check this blog from Willie Favero (well known DB2/zOS specialist):
http://blogs.ittoolbox.com/database/...-objects-11438
which also references an IBM Redbookthat goes into more detail.

HTH.

--
Jeroen

Jul 12 '07 #3