> > > Postgre Database – Log of Message

Postgre Database – Log of Message

2nd October 2016

In previous tips, we’ve navigated through numerous tables in the Cleo Clarify PostgreSQL database, such as the Control Number Generators, Next Numbers, Trading Partner, and GSSV (Global Variables) tables.

Users might need to query inbound/outbound transactions via SQL table.

For users who need to use Dashboard and save queries, this would be a perfect tool. Dashboard does not allow users to save queries and limits the table entries that can be retrieved

select * from “EBI”.“LOG_OF_MESSAGE”

Cleo Clarify Postgre Database Log of Message

POPULAR FIELDS TO RETRIEVE:

STATUS
DATE
MESSAGE_ID
MESSAGE_DIRECTION
ACK_STATUS
USER_REFERENCE_1
USER_REFERENCE_2
USER_REFERENCE_3

Users can query a date range. In our example below we are going to select entries between September 1, 2016 and September 15, 2016

select * from “EBI”.“LOG_OF_MESSAGE” where (“DATE” >= extract(epoch from timestamp ‘2016-09-01 00:00:00’) * 1000) and (“DATE” <= extract(epoch from timestamp '2016-09-15 23:59:59’) * 1000);

Below are the possible fields that can be queried/retrieved.

LOG_OF_MESSAGE FIELDS:

ID
TYPE
STATUS
DATE
MARKED_FOR_PURGE
GROUP_CODE
MESSAGE_ID
MESSAGE_DIRECTION
ACK_STATUS
USER_REFERENCE_1
USER_REFERENCE_2
USER_REFERENCE_3
REFERENCE_DATE_1
REFERENCE_DATE_2
MESSAGE_CONTROL_REFERENCE
CONTROLLING_AGNCY
STANDARD_CLASS
INDUSTRY_GROUP
VERSION_RELEASE
INDUSTRY_GROUP_ID
COMMON_ACCESS_REFERENCE
TRANSFER_STATUS
TRANSFER_SEQUENCE
START_WRAPPED_RECORD
START_WRAP_CHAR_POSITION
START_NONWRAPPED_RECORD
END_NONWRAPPED_RECORD
START_NONWRAP_RECORD_APP
END_NONWRAP_RECORD_APP
SEQUENCE_OF_ASSOC_ACK
PROCESS_USER_REF
METHOD_MESSAGES
DATA_UUID
CONTEXT_ID
IMPLEMENTATION_CONV_REF
ENDPOINT_ID
MESSAGE_SCHEMA_TYPE
SENDER_ID_QUALIFIER
SENDER_ID
RECEIVER_ID_QUALIFIER
RECEIVER_ID
INTERCHANGE_CONTROL_REF
TEST_INDICATOR
GROUP_CONTROL_REFERENCE
USER_REFERENCE_4
USER_REFERENCE_5
ACKNOWLEDGEMENT_ID
ENVELOPE_SCRIPT_PROPERTY_KEY
CODE_LIST_DIR_VERSION
MESSAGE_TYPE_SUB_ID
TIME_SENT
CALC_RETURN_TIME
TIME_RECEIVED
ENDPOINT_NAME
CONNECTION_LOG_NUMBER
CONNECTION_NUMBER
INTERCHANGE_LOG_NUMBER
GROUP_LOG_NUMBER
TRADING_PARTNER_ID
IS_PROCESSED


By: on