Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013

    Unanswered: Cursor and dynamic SQL statement

    Hi all,

    I am new to DB2 and writing a DB2 script to change all INT data type in my database to BIGINT. In SQL Server I can do something as below. But when I ran it on DB2 Command Editor, it always said "DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command..."

    Does anyone know how to change this script to make it work under DB2?


    DECLARE tablename VARCHAR(100)
    DECLARE columnname VARCHAR(100)
    DECLARE sqlstmt VARCHAR(1000)
    DECLARE flag bigint
    declare int_cursor cursor with hold for SELECT t.tabname, c.colname FROM syscat.columns c JOIN syscat.tables t ON c.tabname = t.tabname WHERE c.typename = 'INTEGER' AND t.tabschema= 'my'

    OPEN int_cursor
    FETCH int_cursor INTO tablename, columnname

    SET sqlstmt ='ALTER TABLE my.' + tablename + ' ALTER COLUMN ' + columnname + ' SET DATA TYPE BIGINT'
    EXEC sqlstmt

    until exitcode=1
    END repeat
    CLOSE int_cursor


  2. #2
    Join Date
    Mar 2003
    Use concat function or || operator to concatenate strings ( + is not standard )

    Use execute immediate to execute dynamic statements.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I would expect statements in the block to be terminated with semicolons.
    "It does not work" is not a valid problem statement.

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