CODEWITHSUNDEEP
ibm-midrangedb2-400rpgle
Benny Hill
Benny Hill

Reputation: 6240

CURRENT_TIMESTAMP not always populating

This is DB2 for i running at release 7.3.

I'm currently evaluating what we need to do to convert our DDS-defined physical and logical files to DDL-defined tables and views. As part of my testing I've come across an unusual finding. Given a table with a column defined as a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, sometimes that value populates correctly when new rows are written from an RPGLE program - but most of the time it does not. SQL INSERTs seem to consistently work fine.

Here's the source:

QDDLSRC/SOT

CREATE OR REPLACE TABLE SOT (                                               
  ID   BIGINT        GENERATED ALWAYS AS IDENTITY (START WITH 1),           
  DESC CHAR(20)      NOT NULL DEFAULT '',                                   
  CTS  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,                    
  UTS  TIMESTAMP     NOT NULL FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
  PRIMARY KEY(ID)                                                            
) RCDFMT SOTR;

And to compile it:

RUNSQLSTM SRCFILE(MYLIB/QDDLSRC) SRCMBR(SOT) DFTRDBCOL(MYLIB)

Here's the source for a plain old RPGLE program that does a WRITE:

QRPGLESRC/SOT1 (Plain old RPGLE code)

  CTL-OPT Optimize(*full);
  CTL-OPT Option(*nodebugio : *noshowcpy : *nounref : *srcstmt);
  CTL-OPT Dftactgrp(*no) Actgrp('QILE');
  CTL-OPT Bnddir('QC2LE');
  CTL-OPT Alwnull(*USRCTL);
//-----------------------------
  DCL-F sot disk(*ext) usage(*input : *output : *update) keyed;
//-----------------------------
  DCL-PR sot1 EXTPGM;
  END-PR sot1;

  DCL-PI sot1;
  END-PI;
//-----------------------------
  *INLR = *ON;

  desc = 'RPGLE I 1';
  WRITE sotr;

  CHAIN (1) sot;
  IF %FOUND(sot);
    desc = 'RPGLE U 1';
    UPDATE sotr;
  ENDIF;

  desc = 'RPGLE I 2';
  WRITE sotr;
//-----------------------------

And here's the source for an SQLRPGLE program that does an INSERT - this seems to work just fine.

QRPGLESRC/SOT2 (SQLRPGLE code)

  CTL-OPT Optimize(*full);
  CTL-OPT Option(*nodebugio : *noshowcpy : *nounref : *srcstmt);
  CTL-OPT Dftactgrp(*no) Actgrp('QILE');
  CTL-OPT Alwnull(*USRCTL);
//-----------------------------
  DCL-PR sot2 EXTPGM;
  END-PR sot2;

  DCL-PI sot2;
  END-PI;
//-----------------------------
  EXEC SQL
    INSERT INTO sot (desc)
    VALUES('SQLRPGLE I 1');

  EXEC SQL
    INSERT INTO sot (desc)
    VALUES('SQLRPGLE I 2');

  EXEC SQL
    UPDATE sot
    SET desc = 'SQLRPGLE U 1'
    WHERE id=5;

  *INLR = *ON;
//-----------------------------

And finally, here's how I'm testing. First I INSERT a couple of records via STRSQL:

INSERT INTO sot        
(desc) VALUES('SQL I 1')

INSERT INTO sot        
(desc) VALUES('SQL I 2')

Then execute SOT1 (the plain old RPGLE program): CALL SOT1

Then execute SOT2 (the SQLRPGLE program): CALL SOT2

Then view results (this is from WRKQRY):

Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10..

                            ID   DESC                  CTS                         UTS                      

000001                       1   RPGLE U 1             2018-04-18-16.05.13.520198  2018-04-18-16.05.26.275153

000002                       2   SQL I 2               2018-04-18-16.05.19.670653  2018-04-18-16.05.19.670653

000003                       3   RPGLE I 1             0001-01-01-00.00.00.000000  2018-04-18-16.05.26.274977

000004                       4   RPGLE I 2             2018-04-18-16.05.13.520198  2018-04-18-16.05.26.275196

000005                       5   SQLRPGLE U 1          2018-04-18-16.05.29.244307  2018-04-18-16.05.29.253463

000006                       6   SQLRPGLE I 2          2018-04-18-16.05.29.248723  2018-04-18-16.05.29.248723

****** ********  End of report  ********

Notice that row 3 has an incorrect timestamp value in column CTS. Yet row 4 has the expected timestamp value in CTS.

Any ideas on why this is happening inconsistently?

Upvotes: 0

Views: 1518

Answers (3)

Player1st
Player1st

Reputation: 1605

This is working exactly as expected. Your WRITE from RPG is writing the entire record from table SOT. This includes the CTS field which by default is blank in RPG. The SQL table doesn't need to generate a timestamp because you passed in a timestamp from RPG that was time '0001-01-01 00:00:00.0000'. To say it another way, your RPG is writing the following values into the SQL table:

DESC: RPGLE U 1
CTS: '0001-01-01 00:00:00.0000'
UTS: '0001-01-01 00:00:00.0000'

UTS gets updated every time the table updates so it gets subsequently updated to the current time so it looks correct. CTS only gets updated if nothing was passed but in reality, 0 was passed in this case.

On the other hand, your SQL inserts don't insert anything but the DESC field so the table has to generate the default time from the SQL rules. This will generate the timestamp as you expect. The equivalent statement to what the RPG is doing would look like this instead:

Exec SQL
  Insert Into SOL (DESC, CTS)
  Values ('SQL I 1', Timestamp('00010101000000'));

Upvotes: 5

Richard Evans
Richard Evans

Reputation: 384

In the first record written by SOT1 (with the description "RPGLE I 1"), the CTS timestamp field is never initialized by the program (or you might say it is intitalized as "0001-01-01-00.00.00.000000". In its second write ("RPGLE I 2"), the timestamp is exactly the same as the first record, because the timestamp was initialized by the CHAIN operation to the first record (then never cleared or altered).

The "DEFAULT CURRENT_TIMESTAMP" in the DDL only applies to SQL inserts. The RPG behavior is consistent with its legacy, at least.

Upvotes: 2

Charles
Charles

Reputation: 23783

Working as designed....

DEFAULT comes into play when a isn't passed, but as @Playerfirst mentions, RPG will always pass a value.

Do one of the following
- load the value from RPG using the %timestamp() BIF
- write from RPG through a view/LF that doesn't have the field

Upvotes: 1

Related Questions