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

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.

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

Besides the fact that, as Mr. Wim mentioned, you should be using UPDATE, not INSERT, you're missing a correlation clause:
PS. Using SQL reserved words, like "DATE", as column names is bad...

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^
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.

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

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.

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

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.
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,
...
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.

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