DEFINE TABLE statement
The DEFINE TABLE statement defines how to create a virtual table to
connect multiple updates with the same attributes. You can use DEFINE UPDATE
statement to connect update definitions to the table definition.
Syntax
Parameters
DEFINE TABLE table_name- Specifies the name of the table that you are defining to the System Data Engine. The table name must be unique and consistent with the customized System Data Engine data stream definition name that is defined in the Z Common Data Provider Configuration Tool.
VERSION version- Specifies the version for the table. The maximum length for this value is 18 characters. You might want to specify this optional value for troubleshooting purposes.
FOR &IBM_UPDATE_TARGET;&IBM_CORRELATION;AS &IBM_FILE_FORMAT- These parameters must be included as shown in the syntax.
GROUP BY column-name- Specifies the key fields for this table. These fields must be the same fields as any update
definition that connects to this table. The name and order of these fields must be identical to the
table and any of these updates.Important: The
GROUP BYclause must be consistent with that inDEFINE UPDATEstatement. FILEDS column-name- Specifies the fields required for this table. The column names are the names of the fields required for this table. The column-names must be the same as the names that have been defined in the update definitions that connect to this table.
output-format- Specifies the data type of columns to which the System Data Engine automatically converts.
Use Scenarios
This function is used to join fields from both non-repeated and repeated sections in a single
record. You can define several update definitions to update the same table. Use one update
definition to update the table with the fields from the common section (non-repeated section), and
other update definitions to update the table with the fields from repeated sections. Use
GROUP BY columns to define the key of row on how these fields can be joined
together.
Example
The following example of DEFINE TABLE statement and DEFINE
UPDATE statement specifies how the System Data Engine extracts data from SMF record type 74
subtype 4.
The table A_PM_CF_I is from two update definitions: A_PM_CF update definition is
from the record SMF_074_4 repeat section R744SREQ, and
A_PM_CF1 update definition is from the main record SMF_074_4.
DEFINE TABLE A_PM_CF_I
FOR &IBM_UPDATE_TARGET
AS &IBM_FILE_FORMAT
GROUP BY
(
PERIOD_NAME CHAR (8),
MVS_SYSTEM_ID CHAR (4),
FIELDS
(
REQ_ASYNC_NO FLOAT,
SYSTEM_NAME CHAR (8),
CF_LEVEL SMALLINT);
--------------------------------------------------------
DEFINE UPDATE A_PM_CF
VERSION 'CDP.510'
FROM SMF_074_4 SECTION R744SREQ
TO TABLE A_PM_CF_I
&IBM_FILE_FORMAT
LET
(
T1 = TIMESTAMP (SMF74DAT, SMF74IST) + (
SMF74INT/2000) SECONDS,
D1 = DATE (T1),
P1 = VALUE (PERIOD (SMF74SID, D1, TIME (T1))
, '?')
)
GROUP BY
(
PERIOD_NAME = P1,
MVS_SYSTEM_ID = SMF74SID
)
SET
(
REQ_ASYNC_NO = SUM (R744SARC)
);
-----------------------------------------------------------------
DEFINE UPDATE A_PM_CF1
VERSION 'CDP.510'
FROM SMF_074_4
WHERE SMF74RAN = 0 OR (SMF74RAN = 1 AND
SMF74RSQ = 1)
TO TABLE A_PM_CF_I
&IBM_FILE_FORMAT
LET
(
T1 = TIMESTAMP (SMF74DAT, SMF74IST) + (
SMF74INT/2000) SECONDS,
D1 = DATE (T1),
P1 = VALUE (PERIOD (SMF74SID, D1, TIME (T1))
, '?')
)
GROUP BY
(
PERIOD_NAME = P1,
MVS_SYSTEM_ID = SMF74SID
)
SET
(
SYSTEM_NAME = FIRST (SMF74SNM),
CF_LEVEL = FIRST (R744FLVL)
);