Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: The length of input host variable number 001 is negative or greater than the maximum.

    Hi,

    I am trying to create a native plsql stored procedure in Db2 mainframe & connected using IBM Data studio.

    OS: WINDOWS
    Db2 : 8 version


    Error:
    THE LENGTH OF INPUT HOST VARIABLE NUMBER 001 IS NEGATIVE OR GREATER THAN THE MAXIMUM. SQLCODE=-311, SQLSTATE=22501, DRIVER=4.17.30
    Run of routine failed.
    - Roll back completed successfully.

    I tried a lot to figure out the error, but not able to figure it out.
    Kindly help me out.
    Last edited by HABBIE; 08-31-15 at 03:57.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    where is the TRY_SQLDB_METADATA variable declaration?
    I'm missing the
    DECLARE TRY_SQLDB_METADATA VARCHAR(...);
    statement in the procedure body...
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Hi ,


    I am sorry its wrong post of code. Now i have coded in a accurate way.

    Kindly let me know my mistake

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    Code:
    SET VAR1 = 
      'INSERT INTO MENTIS_SQLDB_METADATA '
    ||'(DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,MAX_ LENGTH,IS_NULLABLE) '
    ||'(select '''||DBNAME||''' as DB_NAME,TAB.NAME as TABLE_NAME,COL.NAME as '
    ||'COLUMN_NAME,COL.COLTYPE as S_COLUMN_TYPE,COL.LENGTH as MAX_LENGTH, '
    ||'COL.NULLS as IS_NULLABLE from SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL '
    ||'where TAB.CREATOR = '''||DBNAME||''' and TAB.NAME not like ''SYS%'' '
    ||'and TAB.CREATOR not like ''SYS%'' and '
    ||'TAB.type = ''T'' and COL.TBCREATOR=TAB.CREATOR)';
    Regards,
    Mark.

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    thanks for the help. But still with the same error


    THE LENGTH OF INPUT HOST VARIABLE NUMBER 001 IS NEGATIVE OR GREATER THAN THE MAXIMUM. SQLCODE=-311, SQLSTATE=22501, DRIVER=4.17.30
    Run of routine failed.
    - Roll back completed successfully.
    Last edited by HABBIE; 08-31-15 at 03:57.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What's the length of the SCHEMA_NAME column of the DBLIST table?
    What's the result of the following query:
    select max(length(SCHEMA_NAME)) from DBLIST
    Regards,
    Mark.

  7. #7
    Join Date
    Jul 2014
    Posts
    294
    Thanks it's resolved

Tags for this Thread

Posting Permissions

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