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