Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: passing table name dynamically to insert during runtime.

    BEGIN

    declare vtablename varchar(50);
    declare vtablecount integer;

    select count(1) into vtablecount from SYSCAT.TABLES Where TABSCHEMA='snerc';

    IF vtablecount>0 Then

    FOR RECORD_COUNT AS select TABNAME from SYSCAT.TABLES Where TABSCHEMA='snerc'
    DO
    SET vtablename=TABNAME;
    insert into snerc.RECORD_COUNT(TABLE_NAME,NUMBER_OF_ROWS) SELECT vtablename ,count(1) from snerc.vtablename;
    END FOR;
    COMMIT;
    END IF;
    END

    when i pass table name dynamically during runtime.

    snerc.vtablename is an undefined name.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    'snerc' shld be in upper case .
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    moreover you need to make the string and execute it
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jul 2008
    Posts
    94
    value in the table is case sensitive.but the object names not case sensitive

    declared a variable for statement.

    set stmt='insert into snerc.RECORD_COUNT(TABLE_NAME,NUMBER_OF_ROWS) SELECT' || vtablename|| ',count(1) from' || 'snerc.'||vtablename
    EXEC SQL stmt;

    trying above.if works will post reply.
    Last edited by laknar; 06-05-09 at 04:40.

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by laknar
    value in the table is case sensitive.but the object names not case sensitive.
    Thats true , but in system tables they are always stored in upper case unless you create table in lower case enclosed in " "
    ..... so TABSCHEMA shld be in upper
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Jul 2008
    Posts
    94
    ok i changed.

    how can i prepare a statement to run

    SELECT COLUMN1 FROM SNERC.VTABLENAME WHERE COLUMN2='ABC'

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You build a string that contains the statement and prepare the string using the PREPARE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2008
    Posts
    94
    I prpared what you suggested.

    when i pass the variable in where clause it is throwing error called.

    S0022(-206)[IBM][CLI Driver][DB2/AIX64] SQL0206N "ABC" is not valid in the context where it is used. SQLSTATE=42703

    because its taking within quates.

    how can i overcome this error.

  9. #9
    Join Date
    Jul 2008
    Posts
    94
    Problem solved.
    Thanks.

Posting Permissions

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