DataMigrator stores log information and flow statistics in an internal data source. These tables may be accessed for read-only purposes through the Server. The tables are optimized for use by DataMigrator and are subject to change by Information Builders. This topic briefly describes the format and function for each column of the tables.
The following tables are described by like-named synonyms.
Flow run-time message log. | |
Run-time statistics for each flow. |
The ETLLOG table contains the DataMigrator run-time message log. The number of lines that are written to this table are managed by the Scheduler Configuration.
Column |
Format |
Function |
---|---|---|
DATE |
Alpha 20 |
Date and time of log entry. |
USER |
Alpha 32 |
User who created the flow. |
JOBNAME |
Alpha 23 |
Unique internal job name. |
APPNAME |
Alpha 64 |
Flow application directory. |
REQ_NAME |
Alpha 64 |
Flow name. |
COUNTER |
Integer 9 |
Line number of the message. |
MESSAGE |
Alpha 80 |
Message strings in the log. |
MSGCODE |
Alpha 12 |
Message code number. |
The ETLSTATS table contains the run-time statistics for each flow.
Column |
Format |
Function |
---|---|---|
S_REQ_ID |
Alpha 23 |
Name of the flow that started this flow. |
S_USERID |
Alpha 32 |
Flow author. |
S_APPNAME |
Alpha 64 |
Flow application directory. |
S_REQ_NAME |
Alpha 64 |
Flow name. |
STAT_SBM_T |
Alpha 20 |
Submit date and time for a flow. |
STAT_STRT_T |
Alpha 20 |
Start date and time of execution. |
STAT_END_T |
Alpha 20 |
End date and time of execution. |
STAT_RTRV_T |
Alpha 20 |
Date and time of retrieval for a flow. |
STAT_FORMAT |
Alpha 15 |
Number of format rejections. |
REQ_RC |
Alpha 16 |
Internal return code for the flow. |
TSCOMID |
Alpha 15 |
TSCOMID for killing a job. |
SESID |
Alpha 15 |
SESID from EDAPRINT. |
ORIG_REQ |
Alpha 32 |
Original flow where dependency started. |
ORIG_USER |
Alpha 32 |
Original user ID running dependent flow. |
DEP_CNT |
Integer |
Dependency number. |
ETL__ERRTXT |
Alpha 80 |
Text explanation of error messages. |
FILLER |
Alpha 30 |
Reserved for internal use. There are two columns with this field name. |
USER_FILE_L |
Alpha 9 |
Reserved for internal use. |
RUN_BY_SCHED |
Alpha 1 |
Scheduled request. |
CPU_TIME |
Alpha 15 |
Total CPU time in milliseconds. |
S_TARG_NAME |
Alpha 64 |
Target name. Note: This and the remaining columns apply on a per target basis. |
STAT_TRANS |
Alpha 15 |
Number of rows processed. |
STAT_ACCEPTS |
Alpha 15 |
Number of rows accepted. |
STAT_NOMATCH |
Alpha 15 |
Number of rows rejected. |
STAT_DBMSERR |
Alpha 15 |
Number of rows rejected by the database for a constraint violation, such as a Unique Index constraint, a Not Null constraint, or a user-specified constraint. |
STAT_INVALID |
Alpha 15 |
Number of rows rejected for failing validation tests. |
STAT_DUPLS |
Alpha 15 |
Number of rejected duplicate records. |
STAT_INPUT |
Alpha 15 |
Number of rows input. |
STAT_CHNGD |
Alpha 15 |
Number of rows updated. |
STAT_DELTD |
Alpha 15 |
Number of rows deleted. |
Note: You must be logged in as a Server Administrator with workspace configuration privileges in order to make any configuration changes, or to follow any of the following procedures.
In release 7.6.x the etllog file is in FOCUS format, which is limited to 2 GB. In release 7.7.x, the etllog file is in XFOCUS format and the limit for this format is 32 GB.
It may be useful for a Server to automate deleting rows from the ETL log and statistics tables. For example, you can delete all rows more than a week old on a daily basis.
-SET &DDATE=AYMD(&YYMD,-7,'I8'); EX ETLDELLS DDATE=&DDATE, DTIME=00:00:00, SUSTART=Y, ETLSTART=Y
Note: The Clean utility rebuilds the ETLLOG. The FDS service has to be stopped to prevent possible file corruption during the rebuild process. Therefore, the following considerations should be taken into account:
The DataMigrator server can be configured so that if there is an error writing to the ETL log, one or more of the following events will occur:
The following procedure shows how to backup and recreate ETL log and statistics.
The following is an explanation of the stored procedures mentioned.
This procedure will create the etllogbk.foc and etlstabk.foc files in the \dm\catalog\ directory. These files are copies of the etllog.foc and etlstats.foc files. Example code for st_backup is:
SHH CRTBACKUP END
This step is optional. This procedure prevents these files from being overwritten during the next backup process. As a result of this procedure, the etllogbk.foc and etlstabk.foc files will be renamed, with a timestamp added to their suffix. For example, etllogbk_20101026_1120258.foc. Example code for st_rename is:
-SET &LOGBK = c:\ibi\srv77\dm\catalog\etllogbk.foc; -SET &STABK = c:\ibi\srv77\dm\catalog\etlstabk.foc; -SET &LOGBKREN = etllogbk || '_ ' || &YYMD || '_ ' || EDIT(&TOD,'99$99$99') || '.foc'; -SET &STABKREN= etlstabk || '_ ' || &YYMD || '_ ' || EDIT(&TOD,'99$99$99') || '.foc'; !REN &LOGBK &LOGBKREN !REN &STABK &STABKREN END
This procedure deletes the original etllog.foc and etlstats.foc files, and will replace them with the new initialized files. Example code for st_recreate is:
EX ETLCRTLS ALL, Y, N END
The process flow should look like the following image.
You can configure the server so that an email message is sent to an address you specify if there is an error writing to the ETL log. For example, even though a log table is automatically maintained so that normal operations prevent it from reaching its maximum size, it is still possible for a flow to generate so many errors that it fills up the log table. This issue can be addressed by having the server send you a message when the log table is full.
Click Customize New Event Routing.
or
Right-click Workspace Logs and select Customize Event Routing.
Click Customize New Event Routing.
The Customize Event Routing dialog box opens, as shown in the following image.
You can manage the log and statistics from the DMC by expanding the Logs and Traces folder. Then, right-click DataMigrator Log and Statistics. A context menu will open with your options, as shown in the following image.
The following options are available in the context menu:
Allows you to view the log and statistics tables.
Creates empty new log and statistics tables.
Allows you to specify a date and time for deletion to occur in the Log and Statistics dialog box, as shown in the following image.
All records previous to the date and time entered are deleted and the log and statistics files are rebuilt. This action frees up the space used by the deleted records so that new records can be added. This does not reduce the size of the log and statistics tables on disk, and new records can still be accepted without growing in size until the newly freed space has been freed up.
Creates a backup copy of the log and statistics tables on the disk and a synonym that can be used to retrieve data from them. These tables are created in the conf/catalog directory that is located on the server (for example, c:\ibi\srv77\dm\catalog). A message will appear in the console showing the location.
Message Code |
Text |
---|---|
ICM18007 |
CPU Time: %1 |
ICM18031 |
Finished |
ICM18040 |
Return Code = xxx |
ICM18072 |
Elapsed run time hh:mm:ss |
ICM18174 |
Agent has been forced to terminate.
|
ICM18175 |
Agent terminated by an operator.
|
ICM18762 |
Job ID: %1 |
ICM18774 |
Request %1/%2 created |
ICM18972 |
------Start of Iterator Log Record for %1/%2------ |
ICM18973 |
------End of Iterator Log Record for %1/%2------ |
ICM18974 |
------Start of Log Record for %1/%2------ |
ICM18975 |
------End of Log Record for %1/%2------ |
ICM18994 |
Iterated job parameters: %1 |
ICM18995 |
The configured maximum number of log lines (%1) has been reached. |
iWay Software |