Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2

    Question Answered: Declare and Execute Variable in Ad-Hoc Query (code is provided)

    I've primarily work in Rapid SQL (Sybase) for over 10 years now but need to use DB2 at times and finding there are differences with declaring variables among other things. Here is an example of what I'm trying to accomplish. It works fine with Declaring my Temp table and manually adding the dates but can't get the Date variables to work of which I can do this easily in Sybase Rapid SQL (different from this of course). Could someone help me understand what I'm missing and how I can get my date variable to execute in the select query?

    I'm using 10.5 DB2.

    Code:
    Begin Atomic    
    Declare v_BeginDate Timestamp;
    Declare v_EndDate Timestamp;
    
    Set 
    v_BeginDate = '8/1/2016',
    v_EndDate = '8/3/2016';
    End
    
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
    TMP_TIME	TIMESTAMP(6)	,
    TMP_ORDER_ID	INTEGER,
    TMP_PRIORITY_CODE	CHARACTER(8
    ) 
    ON COMMIT PRESERVE ROWS not logged with replace;
    
    insert into SESSION.TEST_TABLE 
    select distinct
    ORDER_TIME,
    ORDER_ID,
    NULL
    from
    TABLE 1
    where 
    ORDER_TIME between v_BeginDate and v_EndDate
    
    UPDATE session.test_table T 
    set T.tmp_priority_code = (select S.priority_code from table2 S where S.priority_order_id = T.tmp_order_id);
    
    select * from SESSION.TEST_TABLE;
    
    drop table SESSION.TEST_TABLE;

  2. Best Answer
    Posted by vcs1961

    "I could not get that approach to work but this one did. You did help me immensely to understand where and how to break each set of code and I will continue to refer back to your tip using the Terminator technique if and when I can use that down the road.

    Code:
    drop table SESSION.TEST_TABLE;
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
        TMP_TIME            TIMESTAMP(6),
        TMP_ORDER_ID        INTEGER,
        TMP_PRIORITY_CODE   CHARACTER(8)
    ) 
    on commit preserve rows not logged with replace;
    begin atomic
     declare v_BeginDate timestamp ;
     declare v_EndDate timestamp ;
    
     set v_BeginDate = timestamp('2016-08-01');
     set v_EndDate = timestamp('2016-08-01');
        insert into SESSION.TEST_TABLE 
            select distinct
                   ORDER_TIME,
                   ORDER_ID,
                   NULL
            from table1
            where 
            ORDER_TIME between v_BeginDate and v_EndDate;
    
    [Any updates to this temp table can follow that here];
    
    end;
    select distinct * from SESSION.TEST_TABLE;
    "


  3. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Learn to *always* give the exact error when you post a question. Please adhere to this basic requirement.
    You are using date literals but the value is not in a format acceptable to DB2. Use a locale independent format like YYYY-MM-DD.
    Example '2016-08-01' (if you mean 1st August)
    or '2016-01-08' (if you mean 08th January) and you can also use DATE('2016-08-01') etc .
    You have declared the variables as TIMESTAMP
    so you should then either make a full timestamp in the format that DB2 expects, or use the TIMESTAMP( ) function around the literal, or declare the variables of type DATE.
    Last edited by db2mor; 10-21-16 at 10:49. Reason: correction

  4. #3
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    I apologize and duly noted from this point on. Here are the error messages I get:

    08:18:03.953 DBMS (name of database here) -- [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "Begin Atomic Declare V_BEGIND" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<create_variable>". LINE NUMBER=10. SQLSTATE=42601

    08:18:04.140 DBMS (name of database here)-- [IBM][CLI Driver][DB2/AIX64] SQL0204N "SESSION.DAILY_READ_REP" is an undefined name. SQLSTATE=42704

    So, would Timestamp(8) give the variable the format I need or prefer? Or, I understand the Date type as well that you are recommending. If so, then I still get this error message so I must have the syntax wrong some where to maintain the variable value.

  5. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Configure your development tool with an alternate statement-terminator to distinguish from the default terminator used to end lines inside the block. Use that alternate terminator at the end of each distinct statement.

    Study the IBM samples on your DB2-server in the SAMPLES subdirectories, there are many, it's necessary to get them working in your environment whilst learning the DB2 habits.

    Declare the DGTT outside the block. For sprocs you can declare the DGTT inside the block.

    If the development tool is the command line (example bash on Linux, or db2cmd.exe in Microsoft Windows), your sample in-part could look like this if the normal-terminator is the default semicolon and the statement-terminator is @:

    Code:
    --#SET TERMINATOR @
    --
    -- tell the CLP to stop on the first error and disable autocommit 
    update command options using s on c off@
    
    
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
        TMP_TIME            TIMESTAMP(6),
        TMP_ORDER_ID        INTEGER,
        TMP_PRIORITY_CODE   CHARACTER(8)
    ) 
    on commit preserve rows not logged with replace
    @
    
    begin atomic
     declare v_BeginDate timestamp ;
     declare v_EndDate timestamp ;
    
     set v_BeginDate = timestamp('2016-08-01');
     set v_EndDate = timestamp('2016-08-01');
        insert into SESSION.TEST_TABLE 
            select distinct
                   ORDER_TIME,
                   ORDER_ID,
                   NULL
            from table1
            where 
            ORDER_TIME between v_BeginDate and v_EndDate;
    end
    -- etc rest of code omitted
    
    @

  6. #5
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    I could not get that approach to work but this one did. You did help me immensely to understand where and how to break each set of code and I will continue to refer back to your tip using the Terminator technique if and when I can use that down the road.

    Code:
    drop table SESSION.TEST_TABLE;
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
        TMP_TIME            TIMESTAMP(6),
        TMP_ORDER_ID        INTEGER,
        TMP_PRIORITY_CODE   CHARACTER(8)
    ) 
    on commit preserve rows not logged with replace;
    begin atomic
     declare v_BeginDate timestamp ;
     declare v_EndDate timestamp ;
    
     set v_BeginDate = timestamp('2016-08-01');
     set v_EndDate = timestamp('2016-08-01');
        insert into SESSION.TEST_TABLE 
            select distinct
                   ORDER_TIME,
                   ORDER_ID,
                   NULL
            from table1
            where 
            ORDER_TIME between v_BeginDate and v_EndDate;
    
    [Any updates to this temp table can follow that here];
    
    end;
    select distinct * from SESSION.TEST_TABLE;
    Last edited by vcs1961; 10-24-16 at 16:06.

Posting Permissions

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