Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 table information

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
View previous topic :: :: View next topic  
Author Message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Tue Dec 20, 2011 1:16 am    Post subject: Db2 table information
Reply with quote

Hello Friends,

I am trying to obtain the information from a DB2 table by providing the Unique key field.

I am able to extract only that many row whose total character count is <= 250. (COL_1,COL_2 ---count is 10)


A_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is used.

When I try to obtain the details of additional rows from the table am getting below error after "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'"

"ADDRESS DSNREXX 'column fetched variable names"
"+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
"IRX0030I Error running DB2, line 200: Name or string > 250 characters"

Please let me know how its possible to get all the colums from a table.
    Back to top
    View user's profile Send private message

    Akatsukami

    Global Moderator


    Joined: 03 Oct 2009
    Posts: 1738
    Location: Bloomington, IL

    PostPosted: Tue Dec 20, 2011 1:23 am    Post subject:
    Reply with quote

    That message is coming from Rexx, not DSNREXX or DB2. Notice the unbalanced quotes in your ADDRESS statement.
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Tue Dec 20, 2011 1:26 am    Post subject:
    Reply with quote

    i dont think so.

    I have just removed one column from the db2 select statement and am able to get the information successfully.
    Back to top
    View user's profile Send private message
    dick scherrer

    Site Director


    Joined: 23 Nov 2006
    Posts: 19270
    Location: Inside the Matrix

    PostPosted: Tue Dec 20, 2011 1:34 am    Post subject:
    Reply with quote

    Hello,

    Code:
    "IRX0030I Error running DB2, line 200: Name or string > 250 characters"
    Did removing the column cause the length to become less 250?

    What is the entire line 200?
    Back to top
    View user's profile Send private message
    don.leahy

    Active Member


    Joined: 06 Jul 2010
    Posts: 641
    Location: Whitby, ON, Canada

    PostPosted: Tue Dec 20, 2011 1:39 am    Post subject:
    Reply with quote

    Use HILITE REXX and the cause of the problem will become obvious.
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Tue Dec 20, 2011 1:47 am    Post subject:
    Reply with quote

    SQLSTMT ="SELECT COL_1,COL_2,....." <end>
    SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
    SQLSTMT2 =" WHERE COL_1 ='
    SQLSTMT3 = ""COLVAL"" --> Taken from the user
    SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3

    ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
    failing at this point if the count > 250 as mentioned above.

    Dick S,
    removing the column had made the string character count < 250.

    line 200 is "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
    "
    Back to top
    View user's profile Send private message
    Nic Clouston

    Global Moderator


    Joined: 10 May 2007
    Posts: 1712
    Location: UK

    PostPosted: Tue Dec 20, 2011 2:43 pm    Post subject:
    Reply with quote

    You are missing a closing " on SQLSTMT2 but if this just a typo then do not retype things - cut and paste them. And use the code tags to enhance the readability of your code.
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Tue Dec 20, 2011 9:16 pm    Post subject:
    Reply with quote

    Code:
    CLRSCRN                                               
    address ISREDIT "MACRO"                               
    "SUBCOM DSNREXX"                                     
       say 'Please Enter KEY '
    PULL COLVAL

    S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')

    ADDRESS DSNREXX "CONNECT <db2 test region>"

    SQLSTMT ="SELECT COL_1,COL_2,....." <end>
    SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
    SQLSTMT2 =" WHERE COL_1 ="
    SQLSTMT3 = ""COLVAL"" --> Taken from the user
    SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3

    ADDRESS DSNREXX 'EXECSQL PREPARE A1 FROM :SQLSTMT' 
    ADDRESS DSNREXX 'EXECSQL DECLARE B1 CURSOR FOR A1' 
    ADDRESS DSNREXX 'EXECSQL OPEN B1'       
               
    ENDIND = 'N'
    FORTHEFIRST = 'Y'

    DO UNTIL ENDIND = 'Y'
          ADDRESS DSNREXX 'EXECSQL FETCH B1 INTO :C1,:C2,...,:C14,.."
          IF SQLCODE = 0
              DO
              SAY 'COL_1' C1
               ..<display other columns>
              FORTHEFIRST = 'N'
              ENDIND= 'N'
              END
           ELSE
               IF FORTHEFIRST = 'Y' & SQLCODE =100
                   DO
                   SAY 'NO DATA FOUND'
                   ENDIND = 'Y'
                   END
                ELSE
                   DO
                   SAY 'SQL ERRORCODE =' SQLCODE
                   ENDIND = 'Y'
                   END       
    END
    ADDRESS DSNREXX "DISCONNECT"
    S_RC=RXSUBCOM('DELETE','DSNREXX','DSNREXX')
    EXIT(0)
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Tue Dec 20, 2011 9:17 pm    Post subject:
    Reply with quote

    pasted the entire snippet
    Back to top
    View user's profile Send private message
    Nic Clouston

    Global Moderator


    Joined: 10 May 2007
    Posts: 1712
    Location: UK

    PostPosted: Tue Dec 20, 2011 9:37 pm    Post subject:
    Reply with quote

    You may have pasted it but you did not use the code tags (hint: highlight the code to be 'coded' and click on the Code button). I am not going to waste time downloading your code to my editor to try and see it properly.
    Back to top
    View user's profile Send private message
    enrico-sorichetti

    Global Moderator


    Joined: 14 Mar 2007
    Posts: 10202
    Location: italy

    PostPosted: Tue Dec 20, 2011 9:42 pm    Post subject: Reply to: Db2
    Reply with quote

    post edited to add the code tags, anyway
    the snippet as posted will never run...
    a couple of THEN are missing
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Wed Dec 21, 2011 1:31 am    Post subject:
    Reply with quote

    yes..THEN's are the only things missing....

    The issue is only with the below because if string length is greater than 250 the IF statements are not getting executed at all.

    <code - start>
    S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')

    ADDRESS DSNREXX "CONNECT <db2 test region>"

    SQLSTMT ="SELECT COL_1,COL_2,....." <end>
    SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
    SQLSTMT2 =" WHERE COL_1 ="
    SQLSTMT3 = ""COLVAL"" --> Taken from the user
    SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3

    ADDRESS DSNREXX 'EXECSQL PREPARE A1 FROM :SQLSTMT'

    <code - end>

    after executing the last line of code shown above, IF the total string length in SQLSTMT(SQLSTMT + SQLSTMT1 + SQLSTMT2 + SQLSTMT3) > 250 ,REXX is failing with

    "ADDRESS DSNREXX 'column fetched variable names"
    "+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
    "IRX0030I Error running DB2, line 200: Name or string > 250 characters"

    I feel S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is not able to handle more than 250 characters.
    Back to top
    View user's profile Send private message
    Nic Clouston

    Global Moderator


    Joined: 10 May 2007
    Posts: 1712
    Location: UK

    PostPosted: Wed Dec 21, 2011 2:13 am    Post subject:
    Reply with quote

    RXSUBCON has nothing to do with it - it is only making the Rexx/DB2 interface available (if it is not already - did you first check as per the documentation?).
    Back to top
    View user's profile Send private message
    daveporcelan

    Active Member


    Joined: 01 Dec 2006
    Posts: 645
    Location: Pennsylvania

    PostPosted: Wed Dec 21, 2011 2:24 am    Post subject:
    Reply with quote

    Try coding it like this (notice code tag being used):
    Code:

    SQLSTMT = "SELECT        ",
              " COL_1,       ",
              " COL_2,       ",
              " COL_3,       ",
              " COL_4,       ",
              " COL_5,       ",
              " COL_6,       ",
              " COL_7        ",
              " FROM TABLE_A ",
              " WHERE COL_1 = '"COLVAL"'"
    ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :SQLSTMT"


    I have SQL longer than 1000 characters that work.
    Back to top
    View user's profile Send private message
    Elixir

    Active User


    Joined: 08 Feb 2009
    Posts: 116
    Location: CHENNAI/NEW JERSEY - INDIA/USA

    PostPosted: Wed Dec 21, 2011 2:59 am    Post subject:
    Reply with quote

    It worked for me.
    Back to top
    View user's profile Send private message
    View previous topic :: :: View next topic  
    Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX All times are GMT + 6 Hours
    Page 1 of 1

     

    Search our Forum:

    Similar Topics
    Topic Author Forum Replies Posted
    No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm
    No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
    No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
    No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
    No new posts Need information on COBOL MOVE CORRES... subratarec COBOL Programming 10 Tue Jun 28, 2016 10:38 pm


    Facebook
    Back to Top
     
    Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us