Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2012
    Posts
    45

    Unanswered: ROWNUMBER() not working through ODBC

    Greetings all. I am connecting to DB2 9.2 on zOS with Razor SQL. I cannot get rownumber() to work through any ODBC connections. To narrow it down a bit, I tested a connection to SQL Server and Oracle with Razor SQL over ODBC, and their ROW_NUMBER() functions worked as would be expected. I also tried connecting to DB2 with SQL Server via a linked server, and rownumber() does not work there either. I don't know anything about the mechanics of a connection to a mainframe, so I'm not sure how to pose the question to our DBA's. Has anyone else experienced a similar issue? Thank you.

    Greg

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Are you sure that DB2 on z/OS supports row_number() ?

    Edit:

    According to the manual: http://publib.boulder.ibm.com/infoce...tionsintro.htm

    row_number() is supported (but not rownumber())
    Last edited by shammat; 08-09-12 at 17:48.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    I'm not sure how to pose the question to our DBA's.
    I would start with your exact definition of "not working". Would you expect a medical doctor to be of much use to you if all you said to him was "I'm not feeling well"?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2012
    Posts
    45
    Thank you both so much for the quick replies. In answer to the first question, the first time I looked up row_number for DB2, I saw references to rownumber, so was trying that. I did not realize the true function name is row_number. It makes sense though, since it seems to be a standard with other databases. So, I logged on to TSO, and ran a simple query with row_number, and it works fine as long as I am running it from the mainframe. The errors occur when I connect through ODBC. I've tried connecting with MS Access, SQL Server, and Razor SQL to the same end. So, in answer to the second post, by "not working", I mean I am getting error codes when I try to run it. SQL Server is the only application that returns the DB2 error message, and this is what it says...

    "[IBM][CLI Driver][DB2] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011"

    I was just working with one of our DBA's, and he said he does not know why it would not work through ODBC, and that we don't officially support ODBC, so I may be out of luck. So I found lot's stuff on Google that talked about the heap, but I'm still in the dark about what that is, or if my problem can be fixed. Thank you.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    So, I logged on to TSO, and ran a simple query with row_number, and it works fine as long as I am running it from the mainframe. The errors occur when I connect through ODBC.
    SQL0954C is a DB2 LUW error code, so I'm guessing it comes from the DB2 Connect instance, which you must have in order to connect to a DB2 z/OS datasource using ODBC. Do you know what version of DB2 Connect you're using?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2012
    Posts
    45
    So I got to thinking, maybe the size of the table I'm hitting has something to do with it, so I tried a simple query using a CTE...
    Code:
    WITH x AS (
    SELECT 'A' AS Col1, '2012-08-01' AS Col2 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 'A' AS Col1, '2012-08-02' AS Col2 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 'A' AS Col1, '2012-08-03' AS Col2 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 'B' AS Col1, '2012-08-01' AS Col2 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 'B' AS Col1, '2012-08-02' AS Col2 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 'B' AS Col1, '2012-08-03' AS Col2 FROM SYSIBM.SYSDUMMY1)
    
    SELECT
    	Col1,
    	Col2,
    	ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS RNUM
      FROM x
    Mainframe output
    +---------------------------------------------+
    | COL1 | COL2 | RNUM |
    +---------------------------------------------+
    1_| A | 2012-08-01 | 1 |
    2_| A | 2012-08-02 | 2 |
    3_| A | 2012-08-03 | 3 |
    4_| B | 2012-08-01 | 1 |
    5_| B | 2012-08-02 | 2 |
    6_| B | 2012-08-03 | 3 |
    +---------------------------------------------+

    Indeed, once again, it works as advertized when I run it on the mainframe. It tanks through ODBC, but this time with a different error...

    "[IBM][CLI Driver][DB2] SQL30020N Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements: Reason Code "". SQLSTATE=58009"

    Maybe it has something to do with the driver? We use the same DSN for all ODBC connections to this database, and the driver is what comes with DB2 Connect. Still googling, just thought I'd put it out there. Thanks.

    Greg

  7. #7
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by n_i View Post
    SQL0954C is a DB2 LUW error code, so I'm guessing it comes from the DB2 Connect instance, which you must have in order to connect to a DB2 z/OS datasource using ODBC. Do you know what version of DB2 Connect you're using?
    It looks like I have DB2 Connect Personal Edition 8.1.10. I'm not really sure what it is for, as we only install it to get the driver so we can hit the mainframe through ODBC.

    Greg

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    It looks like I have DB2 Connect Personal Edition 8.1.10.
    Look at the tail end of db2diag.log after you get an error. The file can be found somewhere under \Program Files\IBM\DB2 I presume; I don't have access to a Windows machine with DB2 Connect so can't verify where exactly this file might be, but you should be able to locate it using Windows tools.

    PS. DB2 8 (including DB2 Connect of that version) is not supported by IBM anymore, you may want to consider upgrading to a later version.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Aug 2012
    Posts
    45
    n_i, here is what is in the log for the most recent attempt. It looks like the gist is not enough memory somewhere in the process...

    Code:
    2012-08-10-10.43.41.191000-240 E80705H539         LEVEL: Severe (OS)
    PID     : 1856                 TID  : 3632        PROC : sqlservr.exe
    INSTANCE: DB2                  NODE : 000
    APPID   : GA6C138C.K90E.115700144343
    FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
    CALLED  : OS, -, VirtualAlloc
    OSERR   : 8 "Not enough storage is available to process this command."
    MESSAGE : Private memory and/or virtual address space exhausted
    DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
    49315840
    
    2012-08-10-10.43.41.191000-240 E81246H539         LEVEL: Severe (OS)
    PID     : 1856                 TID  : 3632        PROC : sqlservr.exe
    INSTANCE: DB2                  NODE : 000
    APPID   : GA6C138C.K90E.115700144343
    FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
    CALLED  : OS, -, VirtualAlloc
    OSERR   : 8 "Not enough storage is available to process this command."
    MESSAGE : Private memory and/or virtual address space exhausted
    DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
    49315840
    
    2012-08-10-10.43.41.207000-240 I81787H462         LEVEL: Error
    PID     : 1856                 TID  : 3632        PROC : sqlservr.exe
    INSTANCE: DB2                  NODE : 000
    APPID   : GA6C138C.K90E.115700144343
    FUNCTION: DB2 UDB, DRDA Common, sqljClientBoParseSqlDiagGrp, probe:840
    RETCODE : ZRC=0x8B0F0001=-1961951231=SQLO_NOMEM_APPH
              "No memory available in 'Application Heap'"
              DIA8301C No memory available in the application heap.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, you're running SQL Server, DB2 Connect, and other applications on the same machine. They're competing for memory. How much physical memory do you have?
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Aug 2012
    Posts
    45
    well, I'm not running DB2 Connect as far as I know. We only install it so we can configure the DSN. Other than that, I have never once used it for anything. Now, I do know that SQL Server, since it is set up on my machine as a local database instance is set to run at startup. Perhaps DB2 Connect is doing the same thing without my knowledge. If I am connecting through ODBC using a DSN, does DB2 Connect have to be running? I've got 4 gigs of ram on my machine, And I can tell you SQL Server can handle joins on tables with tens of millions of rows using ROW_NUMBER with no problem.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    If I am connecting through ODBC using a DSN, does DB2 Connect have to be running?
    Yes it does. DB2 Connect is what you may call an application-level gateway, translating DB2 CLI calls originating from the ODBC driver into the DRDA protocol that DB2 z/OS understands, and back. It is basically a specially configured DB2 instance sans databases, with its own memory requirements. According to the error message it fails to allocate about 48 MB of memory for processing of the query that is being sent. There isn't much you can do except to ensure that DB2 Connect has more memory available. For example, you can configure the SQL Server instance to use less memory.


    Quote Originally Posted by gsnidow View Post
    And I can tell you SQL Server can handle joins on tables with tens of millions of rows using ROW_NUMBER with no problem.
    So can DB2. And your point is?
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Aug 2012
    Posts
    45
    Ok, that makes more sense now. My point was only that I know my machine can handle memory intensive queries, not in any way shape or form to imply that one product is better than the other. In any event, I stopped SQL Server from running, and tried the same query using Razor SQL, and it still does not work, but that program does not seem to cause a write to the log file you pointed out. Is there any kind of configuration option I can set in DB2 Connect that might give it more memory? Thank you.

    Greg

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    it still does not work
    I'm being extra patient today. What does the above mean this time? SQL0954C? SQL30020N? Something else?
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Aug 2012
    Posts
    45
    I'm so sorry. I'm frustrated, and I thank you so much for your patience. The error that is generated by Razor SQL (and MS Access), without SQL Server running is this...
    [IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=2203 (#-99999)

    Now, one thing I noticed when running the query in SQL Server is that for an instant before the error message displays in the output pane, I can see one row being displayed, and in the row_number column is a 0. I'm fairly certain a 0 should never be returned by the row_number function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •