Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8

    Unanswered: substr syntax problem

    Hi all,

    I have problems with using "substr" function.

    I wrote the trigger :

    CONNECT TO WAREHOUS^
    CREATE TRIGGER ADMINISTRATOR.SET_DATE AFTER INSERT ON ITMUSER."NT_Processor" FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO ITMUSER."NT_Processor" (DATE)
    VALUES (substr(WRITETIME,6,2)||'-'||substr(WRITETIME,4,2)||'-'||substr(WRITETIME,2,2));
    END^
    CONNECT RESET^

    When i try to save the trigger, it will fail with following error:

    [IBM][CLI Driver][DB2/NT] SQL0206N "WRITETIME" is not valid in
    the context where it is used. LINE NUMBER=3. SQLSTATE=42703


    Does anybody have idea how to fix it ?

    Thanks a lot

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    IMO this is not a "substr" problem. Is WRITETIME a column of ITMUSER."NT_Processor" ?

    Can you give us the DDL (CREATE TABLE script) of ITMUSER."NT_Processor" ?

    The trigger just doesn't make very much sense to me:
    each time a record is added to ITMUSER."NT_Processor" , add another record to the same table ITMUSER."NT_Processor" where only the column DATE gets a value.

    Probably a typo. Perhaps you wanted to UPDATE ITMUSER."NT_Processor".DATE after an insert.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8
    WRITETIME is a column of ITMUSER."NT_Processor".

    I want to fill ITMUSER."NT_Processor".WRITETIME with values derived from ITMUSER."NT_Processor".DATE column using the substr function.

    Here is the DDL:

    ------------------------------------------------
    -- DDL Statements for table "ITMUSER "."NT_Server"
    ------------------------------------------------

    CREATE TABLE "ITMUSER "."NT_Server" (
    "TMZDIFF" INTEGER ,
    "WRITETIME" CHAR(16) ,
    "Server_Name" CHAR(64) ,
    "Timestamp" CHAR(16) ,
    "Blocking_Requests_Rejected" INTEGER ,
    "Bytes_Received/sec" INTEGER ,
    "Bytes_Total/sec" INTEGER ,
    "Bytes_Transmitted/sec" INTEGER ,
    "Context_Blocks_Queued/sec" INTEGER ,
    "Errors_Access_Permissions" INTEGER ,
    "Errors_Granted_Access" INTEGER ,
    "Errors_Logon" INTEGER ,
    "Errors_System" INTEGER ,
    "File_Directory_Searches" INTEGER ,
    "Files_Open" INTEGER ,
    "Files_Opened_Total" INTEGER ,
    "Logon_Total" INTEGER ,
    "Logon/sec" INTEGER ,
    "Pool_Nonpaged_Bytes" INTEGER ,
    "Pool_Nonpaged_Failures" INTEGER ,
    "Pool_Nonpaged_Peak" INTEGER ,
    "Pool_Paged_Bytes" INTEGER ,
    "Pool_Paged_Failures" INTEGER ,
    "Pool_Paged_Peak" INTEGER ,
    "Server_Sessions" INTEGER ,
    "Sessions_Errored_Out" INTEGER ,
    "Sessions_Forced_Off" INTEGER ,
    "Sessions_Logged_Off" INTEGER ,
    "Sessions_Timed_Out" INTEGER ,
    "Work_Item_Shortages" INTEGER )
    IN "USERSPACE1" ;
    ;



    Thanks

    Jan

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Besides the fact that, as Mr. Wim mentioned, you should be using UPDATE, not INSERT, you're missing a correlation clause:
    Code:
    CREATE TRIGGER ADMINISTRATOR.SET_DATE AFTER INSERT ON ITMUSER."NT_Processor" 
    referencing new as new for each statement ...
    begin atomic
    update ... set date=substr(new.writetime...
    PS. Using SQL reserved words, like "DATE", as column names is bad...

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    1) I don't see a column DATE in the CREATE TABLE "ITMUSER "."NT_Server" script, probably you want the system date.
    2) let's look at your trigger:
    CREATE TRIGGER ADMINISTRATOR.SET_DATE
    AFTER INSERT ON ITMUSER."NT_Processor"

    -- after an insert on table ITMUSER."NT_Processor" do something

    FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO ITMUSER."NT_Processor" (DATE)

    -- insert another record in the same table ITMUSER."NT_Processor" , give the column DATE (a column that is not defined in the CREATE TABLE script) the value of WRITETIME:

    VALUES (substr(WRITETIME,6,2)||'-'||substr(WRITETIME,4,2)||'-'||substr(WRITETIME,2,2));
    -- get the substr from the CHAR column
    END^


    I still don't think the logic of this trigger is OK.
    - Adding an extra record to a table each time a record to that table is added and
    - assigning a value to a column DATE that is not present in that table
    still make no sense to me.

    Perhaps what you want to do is :
    SET the value of WRITETIME to the CURRENT DATE of the time the record was added.

    I would suggest to do that in the DDL script (and get rid of that CHAR type to store a DATE value, unless you want to do a lot of substr-things later on)
    "WRITETIME" DATE DEFAULT CURRENT DATE,
    instead of
    "WRITETIME" CHAR(16) ,
    When you do this, writing a trigger is no longer needed.

    If you can't alter the DDL script, you'll have to write a trigger. If you insist on WRITETIME being of type CHAR, don't forget to first convert the CURRENT DATE into a char before applying the substr function.
    You could rewrite the trigger into something more like this:

    CREATE TRIGGER ADMINISTRATOR.SET_DATE
    AFTER INSERT ON ITMUSER."NT_Processor"
    FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    SET WRITETIME = CHAR(CURRENT DATE)
    END^
    I want to fill ITMUSER."NT_Processor".WRITETIME with values derived from ITMUSER."NT_Processor".DATE column using the substr function.
    Is clearly wrong, because there is no DATE column in this table. And as n_i wrote, don't use type names or other SQL reserved words for column names. It is very confusing. And don't use CHAR to store a DATE-type value. There is a reason why there is a DATE-type.

    Rethink what it is that you want to do. If you can put that clearly on paper, we'll be better positioned to help you.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Oct 2005
    Location
    pune(india)
    Posts
    24
    Since everyone mention the same thing for your trigger.

    first of all add a column with DATE as column_name and better try not to use reserved words as mentioned earlier.
    secondly as n_i said do UPDATE instead of doin insert there.
    CHAR is not the correct usage here either use DATE type or varchar for WORKTIME column.

    If you are using system current date then use VARCHAR for WORKTIME.

    Thanks,
    sinwar

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    CHAR is not the correct usage here either use DATE type or varchar for WORKTIME column. If you are using system current date then use VARCHAR for WORKTIME
    Why would you use a VARCHAR to store a date ? In what differs this from using a CHAR to store a date ?

    During our Y2K cleanup I have converted all the CHAR(8) ('YY/MM/DD', 'MM-DD-YY', 'UNKNOWN ', '3thSTREE', 'january ', ... and all the other possible things you can put in a string) dates to DATE. Great job, I recall. I even discovered integers and decimals that were stored as CHAR.

    The biggest advantage for the user is that he can put enything he wants into a VARCHAR or CHAR field, especially when the program doesn't verify the input.

    It's all to keep the folks from the IT department sharp.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8
    Hi all,

    thank you for your advices ... i changed my wishes according to your mentions:

    I have Timestamp(character 16 - is presented in the script bellow) column in the format : cyymmddhhmmssttt where:
    c = century (1 = 21st century)
    yymmdd = year, month, day
    hhmmssttt = hours, minutes, seconds, milliseconds

    And i would like to write a trigger which will fill timestamp_date(DATE) and timestamp_time columns (which will I create) with the appropriate values derived from the timestamp for better sorting.

    CREATE TRIGGER ADMINISTRATOR.SET_DATE AFTER INSERT ON ITMUSER."NT_Processor"
    begin atomic
    update ITMUSER."NT_Processor"
    set timestamp_time=substr(TIMESTAMP,6,2)||'-'||substr(TIMESTAMP,4,2)||'-'||substr(TIMESTAMP,2,2)
    SET timestamp_date=...

    Am I right ?

    Thanks

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Why do you insist on storing dates and timestamps in a CHAR column ? Have you thought about the consequences? Do you like to work with SUBSTR that much ?

    There can be a reason why you want to store them the way you intend. Can you tell us why ? You mentioned "for better sorting", you can use a TIMESTAMP column to sort.

    If you store them in CHAR format and a question is: how much time passed between the first and the last inserts per day. How are you going to calculate that ? Converting SUBSTRings to integers taking care of underflows (14 min 13 sec - 12 min 56 sec should first become 13 min 73 sec - 12 min 56 to make the subtraction of the seconds work. What if the underflow passes over to the hours, or days or weeks or months or years ? You're openening a hell's pit. Happy debugging.

    That's why most use the special data types that DB2 provides and that have worked flawlessly for years, like DATE and TIMESTAMP.
    Code:
    CREATE TRIGGER ADMINISTRATOR.SET_DATE AFTER INSERT ON ITMUSER."NT_Processor"
    begin atomic
    update ITMUSER."NT_Processor"
    set timestamp_time=substr(TIMESTAMP,6,2)||'-'||substr(TIMESTAMP,4,2)||'-'||substr(TIMESTAMP,2,2)
    SET timestamp_date=...
    When I look at your code, I think you're very new to SQL and DB2. A timestamp column also includes the date, so there is no need for two separate coulumns timestamp_time and timestamp_date, just one eg. TS_CREATE. If you want to store the timestamp when a record was inserted, I suggest to use the DEFAULT clause I mentioned before, it works with the least trubbles:

    CREATE TABLE ...
    ...
    TS_CREATE TIMESTAMP DEFAULT CURRENT TIMESTAMP,
    ...
    timestamp_time=substr(TIMESTAMP,6,2)||'-'||substr(TIMESTAMP,4,2)||'-'||substr(TIMESTAMP,2,2)
    substr(TIMESTAMP,6,2) will give a DB2 error. TIMESTAMP is the type of a column. What you need/mean is CURRENT TIMESTAMP, the current date and time when you submit your SQL script to DB2.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8
    I think there is a misunderstood - I should explain some thinhs to you :

    Name of the column (Timestamp) and data type (Char) is defined by IBM Tivoli, I have no chance to change it.
    I need the derived values for using in reports - date and time.

    Weel, can zou help me please ???

    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
  •