DB2 Equivalant for Rownum of Oracle

Few questions on DB2 equivalants for Oracle

1. What is the DB2 Equivalant of RowNum in Oracle.

2. I used to run SQLLoader command (SQLLDR) in my PC accessing Oracle in Unix. What is the equivalant of SQLLDR command in DB2 and can it be run from PC accessing DB2 in zOS.

thanks
Raja

1. What is the DB2 Equivalant of RowNum in Oracle.

ROW_NUMBER()

Take a look at the bottom of the following link: http://forums.devshed.com/t157033/s.html

2. What is the equivalant of SQLLDR command in DB2 and can it be run from PC accessing DB2 in zOS.

db2move

You might also want to look at this .

Thanks for the reply..

When I used row_number() in the select statement - I am getting following error.

SQL error. Stmt #: 5492 Error Position: 0 Return: 8601 - [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "ROW_NUMBER " of type "" having compatible arguments was found. SQLSTATE=42884
(SQLSTATE 42884) -440

here is the SQL I have used..

SELECT A.LIMIT_TYPE, row_number()
FROM PS_LIMIT_TBL A
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_LIMIT_TBL A_ED
WHERE A.LIMIT_TYPE = A_ED.LIMIT_TYPE
AND A_ED.EFFDT <= CURRENT DATE)

Raja

I think the ROW_NUMBER function is a fairly new feature. What version of DB2 are you using?

Ver 7.1.

Regards

Raja Nandam

According to this web page:

http://www-106.ibm.com/developerwor...e/0110lyle.html

ROW_NUMBER was available as far back as the 6.x versions of DB2.

Every time I've seen the ROW_NUMBER function used, it is in conjunction with the OVER keyword. Try running your query by relating the ROW_NUMBER to a field and see if it works:

SELECT A.LIMIT_TYPE, row_number() OVER A.LIMIT_TYPE AS RN
FROM PS_LIMIT_TBL A
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_LIMIT_TBL A_ED
WHERE A.LIMIT_TYPE = A_ED.LIMIT_TYPE
AND A_ED.EFFDT <= CURRENT DATE)

Nope. I tried the SQL got the following error.

SQL error. Stmt #: 5492 Error Position: 0 Return: 8601 - [IBM][CLI Driver][DB2] SQL0104N An unexpected token "A" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601
(SQLSTATE 42601) -104

Here is the SQL I tried.

SELECT A.LIMIT_TYPE, row_number() OVER A.LIMIT_TYPE AS RN FROM PS_LIMIT_TBL A WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_LIMIT_TBL A_ED WHERE A.LIMIT_TYPE = A_ED.LIMIT_TYPE AND A_ED.EFFDT <= CURRENT DATE)

Does a query like this give you the correct results?

SELECT A.LIMIT_TYPE, row_number() OVER A.LIMIT_TYPE AS RN
FROM PS_LIMIT_TBL A
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM PS_LIMIT_TBL A_ED
INNER JOIN PS_LIMIT_TBL B
ON A_ED.LIMIT_TYPE = B.LIMIT_TYPE
WHERE A_ED.EFFDT <= CURRENT DATE)

Nope.

I have asked my DBA about the issue today and he was mentioning ROW_NUMBER() function is only valid for Windows and UNIX environments and not in DB2 Mainframe.

He was mentioning even the Sequence Number functionality is also not in DB2 Mainframe environment.

I really appreciate your effort to help me out.

I will plan for a program like SQR instead of SQL to get the desired result.

Regards
Raja

It would surprise me if IBM would remove OLAP functionality from DB2 on their most powerful hardware platform. I'm not saying your DBA is wrong because I'm sure he knows a lot more than I do. I've only used the ROW_NUMBER function on Linux/s390 so I don't have any experience using it under mainframe operating systems.

Just to humor me (and for my own knowledge) could you run a query for me on your machine? This query doesn't do anything meaningful, but it is a good test of the ROW_NUMBER function:

WITH DEVSHEDTEST AS
(SELECT LIMIT_TYPE, ROW_NUMBER() OVER (ORDER BY LIMIT_TYPE) AS RN
FROM PS_LIMIT_TBL)
SELECT LIMIT_TYPE, RN
FROM DEVSHEDTEST
WHERE RN BETWEEN 2 AND 7

Got the following error message -

Error: SQL0199N The use of the reserved word "AS" following "" is not valid. Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ". SQLSTATE=42601
(State:42601, Native Code: FFFFFF39)

Regs
Raja

Hello there.

Quick question, is there an equivalent to rownum for Firebird databases ?

Thanks

For DB2, try
select INT(ROWNUMBER() OVER ()) AS RNUM ....

Hmm - I think there are a good number of things you can do on DB2 for LUW(Linux Unix Windows) that you cannot do on DB2 mainframe (Z/OS), such as sequences. As such, UDB is constantly way ahead of mainframe DB2!

fv