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

    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?

    Thanks!!
    ---------------------------------------------------------------------
    BEGIN

    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
    repeat
    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

    END

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

    Use execute immediate to execute dynamic statements.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    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
  •