IBM Security Z Security

 View Only
Expand all | Collapse all

Export SMF 119(11) records with re-usable timestamp format for DB2

  • 1.  Export SMF 119(11) records with re-usable timestamp format for DB2

    Posted Thu December 08, 2022 04:57 AM
    Hello

    As there is currently no out of the box function to analyze SMF 119(11) records we developed a report on our own to load all data into DB2 tables and perform long-term analysis there. That works quite well however the filter on timestamps is not that easy. The format when loading from type=SMF is as follows:

    *Following is not the full code and just a snippet where the connection_initiation and _termination is used.
      n type=smf n=smfsel outlim=0                         
     S type=119(11) and (sa_event_type=(Connection_initiation, Connection_termination)       
    ​list type                                            
     n type=smf noaction nodup pl=0 nopage               
    s likelist=SMFSEL                                    
    sortlist ,                                           
       CONNECTION_INIT_DATETIME(0) ,
       CONNECTION_END_DATETIME(0)  

    The time format reported for those is as follows:
    Connection_initiation --> "15 Nov 2022 06:25:56.59"
    Connection_termination -->"  6 Dec 2022 05:13:33.90"

    However, that format cannot be loaded into DB as this does not meet the DB2 TIMESTAMP format that is expected to be like:"9999-01-01-01.01.01.000001"

    Is there any way to transform the data into a proper DB2 TIMESTAMP format so when creating a CSV with SORTLIST it can be loaded directly into the DB2 column defined as TIMESTAMP with the DB2 load utility?

    There are ways to perform that in DB2 with a new column that will be updated later like:
    SELECT TIMESTAMP_FORMAT(' 5 Nov 2022 06:25:56.00','DD MON YYYY HH24:MI:SS.FF') AS FORMATED
    FROM sysibm.sysdummy1

    However, the preferred way is to export the data already in SORTLIST in the correct format to avoid that additional step.

    regards
    marco



    ------------------------------
    Marco Egli
    ------------------------------


  • 2.  RE: Export SMF 119(11) records with re-usable timestamp format for DB2

    IBM Champion
    Posted Thu December 08, 2022 07:31 AM
    Edited by Rob van Hoboken Thu December 08, 2022 07:31 AM
    Hi Marco
    can your Db2 application ingest XML standard timestamps?  zSecure offers the XSD_DATETIME format that looks like 2022-12-08T13:27:00.00+01:00 or if you add an overriding length of 22 it should look like: 2022-12-08T13:27:00.00

    sortlist ,                                           
       CONNECTION_INIT_DATETIME(xsd_datetime,22) ,
       CONNECTION_END_DATETIME(xsd_datetime,22)  ​

    ------------------------------
    Rob van Hoboken
    ------------------------------


  • 3.  RE: Export SMF 119(11) records with re-usable timestamp format for DB2

    Posted Thu December 08, 2022 07:52 AM

    Hi Rob

    Thanks for the fast reply! IBM's DB2 supports the following format as timestamp and documented here: https://www.ibm.com/docs/en/db2-for-zos/12?topic=values-timestamp
    I tested as well the format as suggest as " 2022-12-08T13:27:00.00" that fails with the same error. Would it be possible to work somehow with carlas define option to construct the format that is expected by Db2?



    ------------------------------
    Marco Egli
    ------------------------------