IBM i Global

 View Only
Expand all | Collapse all

Adding Timestamp column

  • 1.  Adding Timestamp column

    Posted Fri February 25, 2022 12:27 PM
    Hi,

    I want to add a timestamp column at the end of file, the file is level check no.

    I hoped than adding a column without recompiling all my programs it can work, 
    but if a program made a write it goes in error, message code is: RNQ1299 

    I try with an alfa column an there is no problem, Is there a way to put a current timestamp to this column without having to recompile all the programs who are using that file? about 200 (the great part only in read). 

    Many thanks


    ------------------------------
    Paolo Salvatore
    ------------------------------


  • 2.  RE: Adding Timestamp column

    IBM Champion
    Posted Sun February 27, 2022 08:28 PM
    Edited by Satid Singkorapoom Sun February 27, 2022 08:41 PM
    Dear Paolo

    Did you check that the program you ran accessed ONLY the file you added the timestamp column?  If not, the error you received may have been from another file that the program also accessed. 

    You need to see the actual error and RNQ1299 is not one such error.  To look for the exact error, please look into the job log and display the detail of RNQ1299. In "Cause" section of the detail, you identify IBM i message ID indicated in this section. Then back to the job log, you display the detail of that IBM i message ID (very likely, this message appears right before RNQ1299 in the job log) and post the entire detail of that message here which may provide more useful information for analysis.  

    A sample of RNQ1299 detail looks like this :

     Message ID . . . . . . : RNQ1299 Severity . . . . . . . : 99
     Message type . . . . . : Inquiry
     Date sent . . . . . . : 01/09/01 Time sent . . . . . . : 15:02:
    >
     Message . . . . : I/O error CPF5257 was detected in file ECINQDSP (C G D F).
     Cause . . . . . : The RPG procedure ECINQPGM in program EDITST/ECINQPGM
     received the message CPF5257 at statement 020000 while performing I/O
     operation EXFMT on file ECINQDSP. The actual file is EDITST/ECINQDSP.
     Recovery . . . : Check the job log for more information on the cause of the
     error and contact the person responsible for program maintenance.


    ------------------------------
    Satid Singkorapoom
    ------------------------------

    Original Message


  • 3.  RE: Adding Timestamp column

    IBM Champion
    Posted Sun February 27, 2022 09:10 PM
    Dear Paolo

    Additional search led me to this article that I ask you to read as it may be useful for your case.  The next-to-last paragraph may be particularly useful for you here. 
    https://www.mcpressonline.com/analytics-cognitive/db2/techtip-simplify-your-development-with-db2-automatic-timestamp-support

    Not sure if this article may also help or not:  https://www.mcpressonline.com/programming/rpg/practical-rpg-manipulating-timestamps

    ------------------------------
    Satid Singkorapoom
    ------------------------------

    Original Message


  • 4.  RE: Adding Timestamp column

    Posted Mon February 28, 2022 07:32 AM
    Paolo,

    1.  Make sure your timestamp definitions (the one you have defined in your file and the one you are trying to write to it/read from it), match up.  In other words, make sure they have the same format, number of microseconds, delimiters etc...

    2.  Consider spending a little extra time and do a DDS -> DDL conversion.  The end result should be a set of older logicals that have no idea about the new column(s) and therefore no recompiles necessary.  If you need to make the new column available to a program, create a new logical or a view to access it.  

    Hope this helps,
    Mark.

    ------------------------------
    Mark Soulje
    ------------------------------

    Original Message


  • 5.  RE: Adding Timestamp column

    Posted Tue March 01, 2022 11:57 AM

    Hi all,

    many thanks for your answer, I open a case in IBM and now I'm waiting for an answer from IBM support.

    I try with a simple case to replicate my problem, this are the dds:

                 R REC1                                     
                                                        
               CONTATORE      9B 0                      
               CAMPOTEST    100          VARLEN         
                      
             K CONTATORE


    And this is my rpgle:

    **free                                              
Dcl-F TestFile1 Usage(*Input :*Output :*Update);    
                                                    
Contatore = 1;                                      
CampoTest = 'Prova numero : ' + %Char(Contatore);   
                          
                                                    
Write Rec1;                                         
                                                    
*inlr = *On;


    Then I add a timestamp to my pf:

                   R REC1                                           
                                                                
                 CONTATORE      9B 0                            
                 CAMPOTEST    100          VARLEN               
                 DATAORA         Z                              
               K CONTATORE


    So I made the chgpf:

    CHGPF FILE(MYLIB/TESTFILE1) SRCFILE(MYLIB/QDDSSRC)

    and running my program without recompiling it I obtain this error:

    Data mapping error on member TESTFILE1.
    Data mapping error on member TESTFILE1.
    ? C
    Data mapping error on member TESTFILE1.
    ? C
    I/O error CPF5029 was detected in file TESTFILE1.
    I/O error CPF5029 was detected in file TESTFILE1 (C G D F).
    ? C
    I/O error CPF5029 was detected in file TESTFILE1 (C G D F).
    ? C
    Application error. RNX1299 unmonitored by TESTFILE1 at statement
    0000000011, instruction X'0000'.

    my job have dateformat: *YMD, dateseperator: '-'   timesep: '.'



    ------------------------------
    Paolo Salvatore
    ------------------------------

    Original Message


  • 6.  RE: Adding Timestamp column

    Posted Tue March 01, 2022 12:07 PM
    Hi Paolo,

    I would advise to add a default value to your field description before adding the field to the file.

    I would think, that the system wants to have a valid value in the timestamp field - but adding it with a short buffer would add *blanks - which is valid for a char field, but not for a timestamp. 

    Can you try the same with a zoned, packed or integer field? It should give also a data mapping error, because *blank is also illegal for these data types. 

    Regards
    Daniel

    Von meinem iPhone gesendet



    Original Message


  • 7.  RE: Adding Timestamp column

    Posted Wed March 02, 2022 02:45 AM
    This may or may not apply...
    Had an issue a while ago   the date field was defined with YMMDD  and OS upgrades and PTFs  required the field def as YYMMDD.   Fixed a retrieval issue

    ------------------------------
    Rick Eggleston
    ------------------------------

    Original Message


  • 8.  RE: Adding Timestamp column

    Posted Wed March 02, 2022 03:28 AM
    The typical problems with timestamps are:

    - the timestamp ist stored in a string-like format - but it is no string, because it has a fixed format

    - the lowest value is not „0000-00-00...." but „0001-01-01...." as well the highest value is not „9999-99-99...." but „9999-12-31...."

    Therefore a timestamp field should always have a „default value". The same goes for date and time fields IMHO. 

    Von meinem iPhone gesendet



    Original Message


  • 9.  RE: Adding Timestamp column

    Posted Thu March 03, 2022 10:15 AM
    Hi Paolo,

    (Sorry in advance for my Bad english -_-')

    Like Mark said, you should try to modernise your PF (DDS to SQL). If you need documentation, don't hesitate to Ask.

    Another solution (which IS very dirty) : directly in SQL, you can add SQL column type on a PF DDS, something like this :
    Alter table xxx add column data timestamp default on row update etc...

    It's Dirty because you will mix DDS définition and SQL DDL to reverse your object. Also, if you do so, you should rewrite your PGM with only SQL...

    So I recommand you to modernise your PF. For 1 table without tool, you Can Do it on 1-2 days ​

    ------------------------------
    Andy Malo
    CAFAT
    New-Caledonia
    ------------------------------

    Original Message


  • 10.  RE: Adding Timestamp column

    Posted Fri March 04, 2022 09:29 AM
    In joblog there should be a error message CPF5035 - Data mapping error on member TESTFILE1
    Cause . . . . . : A data mapping error occurred on field DATAORA in record
    number 0, record format REC1, member number 1, in member TESTFILE in library YOURLIBRARY ,
    because of error code 17. The error code
    17 -- The format of the data in a date, time, or timestamp field is not
    valid.

    if you add CTL-OPT ALWNULL(*UsrCtl) ;
    To allow a null value to your rpg prgram with write and update then your DDS specification should work.
    You do not indicate whate timestamp filed is used for.
    If it is as audit field for when record was update you may want to consider
    SQL ALTER TABLE TESTFILE1 statement with
    ADD COLUMN AUDIT_TIME FOR "AUDITTIME" TIMESTAMP
    FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL ;

    If audit useage is true you may want to see https://www.rpgpgm.com/2017/01/using-auditing-columns-to-audit-data.html
    I find these audit field extremely useful and a great addition.
    https://www.ibm.com/docs/en/i/7.3?topic=language-creating-auditing-columns

    ------------------------------
    Glenn Gross
    ------------------------------

    Original Message