Technique for Detecting and Handling Invalid Data via TIBCO® Object Service Broker
Article ID: KB0089392
Updated On:
| Products | Versions |
|---|---|
| TIBCO Object Service Broker for z/OS | - |
| Not Applicable | - |
Description
Resolution:
Description:
============
Using TIBCO® Object Service Broker (OSB) to process external data can provide many benefits but occasionally challenges. When dealing with Packed Data, OSB tables defined with "Packed Decimal" syntaxes such as L, O or P will fail if the external data in a row does not contain a valid packed value. Often legacy COBOL or IMS data will be initialized to low-values (binary zeroes) prior to loading. If a packed field is initialized to low-values, then conversion of the field to OSB packed format will fail.
This document will illustrate a technique for handling bad data, specifically packed data. This technique can be generalized to allow processing and cleansing of other syntaxes as well.
Environment:
============
* all environments where TIBCO Object Service Broker can be executed
* accessing legacy external data which might not contain valid data for the syntax used by OSB
Symptoms:
=========
When an attempt is made to convert invalid packed data, the following errors are typically observed:
* Unable to EDIT or BROWSE; Buffer conversion error for subview "tablename"
* S6BIM220E <tablename> <terminal> IMSROW: Conversion error: IMS to internal, field: "PACKED" Packed field contains non-numeric data.
Cause:
======
In the case of packed decimal data, the format of such data is strictly defined. Reference the IBM manual "z/Architecture Principles of Operation" section "Packed-Decimal Formats" for information on acceptable packed decimal formats. When packed data is manipulated by OSB, hardware instructions may be used to move or perform arithmetic operations on the data. If the data is not valid, these operations will fail and result in error messages being issued as previously documented.
Resolution:
===========
One method to deal with packed data conversion failures is to use the power of OSB Subview tables to map the data and allow a rule to process the field. Let us consider the case where we are processing data from a z/OS sequential dataset using an Import table and Subview along with a field Derivation Rule. A Derivation Rule allows execution of rule logic in processing the field data. Since this type of processing must happen in an Execution Environment (EE), how can conversion errors in a database gateway be handled? One answer is to use a special syntax of Raw Data (RD) for problematic packed fields and then define a Subview over the base table with a packed syntax and Derivation Rule. An example will illustrate the point.
Let us consider a case where we have a packed field which in most cases contains valid packed data but in a few rows, the data is invalid; perhaps, containing low-values, spaces or other uninitialized residual data. In rows that have valid packed data, we wish to process the data as found; but, in cases where the data is not valid, we only want to treat the field as if it were "NULL". NULL in OSB represents a field which has no data assigned to it. This is different than assigning a value of zero to the field which is a legitimate packed decimal numeric value.
Sample Definitions:
===================
To illustrate this technique, we need to create the following objects:
1) base IMP (IMPORT) table describing the input sequential file
2) Subview table to re-map the IMP data and allow a Derivation Rule to be executed
3) Derivation Rule
1) Define Base IMP Table. This table maps the problematic packed data field, called PACKED, to syntax Raw Data "RD" so that no conversion occurs while accessing the sequential dataset. In our example, the packed field exists in columns 1-4 of the sequential dataset. For illustrative purposes, the only other field is a string field, called COMMENT, containing comments. An IDGEN key has been defined to allow Browsing against the base IMP table but the virtual IDGEN key field does not actually exist in the IMP sequential dataset. The Table Definer definition would look like:
TABLE DEFINITION
Table: SOL1_BOXEWB_IMP Type: IMP Unit: EZL30 IDgen: Y
Source:
File: S6B.OSB52.IMPORT.DATA
DDname: External Routine Name:
Server ID:
Parameter Name Typ Syn Len Dec Class Src ' Event Rule Typ Acc
---------------- - -- --- -- - - ' ---------------- - -
_ LOCATION I C 16 0 L ' _
_ ' _
-------- IMP ---------|---------- Metadata Definition ------
Field Name Xsyn Xlen Xdec Offset Key Typ Syn Len Dec Ord Rqd Default
---------------- ---- ----- -- ----- - - -- ----- -- - - --------
_ IDGEN B 4 0 0 P I B 4 0
_ PACKED H 4 0 4 RD 9 0
_ COMMENT V 50 0 8 V 50 0
2) Define the Subview Table. The Subview definition redefines the problematic field(s) and provides a Derivation Rule that will be invoked when the associated field is accessed. The Derivation Rule will be able to analyze and provide the final value for the field.
The Table Definer definition would look like:
TABLE DEFINITION 0
Table: SOL1_BOXEWB_SUB Type: SUB Unit: EZL30 IDgen: N
Source: SOL1_BOXEWB_IMP
Select:
Lock Mode: D
Parameter Name Typ Syn Len Dec Class Src Source Name Default
---------------- - -- --- -- - - ---------------- ----------------
_ LOCATION 0 0 L
_
Synt Dec Order
Field Name Type Len Key Rqd Default Src Source Name
---------------- - -- ----- -- - - - ---------------- - ----------------
_ IDGEN I B 4 0 P
_ PACKED P 4 0 D SOL1_BOXEWB_RUL
_ COMMENT V 50 0
3) Define the Derivation Rule. The Derivation Rule is the location of the business rules that define how the packed data will be treated. The Derivation Rule makes the decisions on what value is extracted and presented to the user when accessing the IMP data. In our rule, we implement the business rules for the field PACKED. The business rules are:
* If the field PACKED contains valid packed data, return the valid data as the field value; otherwise, return the value "NULL" to indicate that the field does not contain valid data.
The Rule Definer definition could look like this:
RULE EDITOR ===> SCROLL: P
SOL1_BOXEWB_RUL;
_
_ ---------------------------------------------------------------------------
_ ------------------------------------------------------------+--------------
_ RETURN($TYPECAST('', 'P', 4, 0, FLDVAL(EVENTTABLE, ¦ 1
_ EVENTFIELD)) + 0); ¦
_ ---------------------------------------------------------------------------
_ ON ERROR :
_ RETURN(NULL);
The Derivation Rule is invoked when data is being assigned to the associated Subview field PACKED. The builtins EVENTTABLE and EVENTFIELD provide the name of the subview table and subview field being processed. Thus one Derivation Rule can be written to process many fields even in different tables. The $TYPECAST builtin is used coerce the Raw Data (RD) field PACKED to a packed format. If the field contains valid packed data, the coercion will succeed. If the field value is not a valid packed value, the coercion will fail and raise an "ON ERROR" condition which will cause the "ON ERROR" condition handler to be invoked and RETURN a value of NULL for the subview table field being processed.
4) Define the IMP input dataset to z/OS. For this example, a fixed block dataset called 'S6B.OSB52.IMPORT.DATA' was used. The physical characteristics of the dataset were:
Organization . . . : PS
Record format . . . : FB
Record length . . . : 80
Block size . . . . : 27920
and the data loaded into the dataset as displayed by TSO/ISPF browse was:
----+----1----+----2----+----3----+----4----+----5----+
REC#1: @This is a good 4 byte packed field: x'1234567C'
1357E88A48A48489984F48AA8498898848889874A7FFFFFFFC74444
246C3892092010766404028350713254069534A07D12345673D0000
-------------------------------------------------------
REC#2: q Another good 4 byte packed field: x'9876543C'
9753C99A889489984F48AA8498898848889874A7FFFFFFFC7444444
864C15638590766404028350713254069534A07D98765433D000000
-------------------------------------------------------
REC#3: [Ü[[This is a bad 4 byte packed field: x'BADBADBA'
BDABE88A48A4848884F48AA8498898848889874A7CCCCCCCC744444
ABDA389209201021404028350713254069534A07D21421421D00000
-------------------------------------------------------
----+----1----+----2----+----3----+----4----+----5----+
In the above data, the fields are defined as follows:
======== ========= ========= ===============
Beginning Ending
Field Column Column Description
======== ========= ========= ===============
IDGEN n/a n/a IDGEN is a virtual key field
PACKED 1 4 packed decimal. Third row has bad data
COMMENT 5 55 information concerning PACKED field
5) Now we can view the original contents of the base IMP table with the OSB Workbench. OSB converts the PACKED field data from syntax "RD" to a hexadecimal displayable value but no arithmetic operations could be performed against the PACKED field as defined with syntax "RD". The Browser shows that the third row contains invalid data for the field PACKED:
BROWSING TABLE : SOL1_BOXEWB_IMP
COMMAND ==>
SCROLL: P
IDGEN PACKED COMMENT
_ ----------- ---------- --------------------------------------------------
_ 1 1234567C This is a good 4 byte packed field: x'1234567C'
_ 2 9876543C Another good 4 byte packed field: x'9876543C'
_ 3 BADBADBA This is a bad 4 byte packed field: x'BADBADBA'
6) Browsing the Subview definition will show the effects of the Subview and Derivation Rule. We execute the rule STEBROWSE(SOL1_BOXEWB_SUB ) so as to avoid the Workbench changing the search path for our Derivation Rule. The results are:
BROWSING TABLE : SOL1_BOXEWB_SUB
COMMAND ==>
SCROLL: P
IDGEN PACKED COMMENT
_ ----------- -------- --------------------------------------------------
_ 1 1234567 This is a good 4 byte packed field: x'1234567C'
_ 2 9876543 Another good 4 byte packed field: x'9876543C'
_ 3 This is a bad 4 byte packed field: x'BADBADBA'
Browsing the SUB (Subview) table shows that the PACKED field value for the third row has now been set to NULL since the original IMP field did not contain a valid packed value. Contrast this with the data shown when browsing the base IMP table. Note also that the displayed packed field values are now integer numbers and not hexadecimal representations of the field value. The trailing sign nibble x'C' has also been removed and is reflected in the sign of the integer value.
Warning:
========
Customers using this technique with IMS should open a Service Request and ask to be notified when a fix is available for problem report:
OSBZ-1295 3270 Table Definer does not support Syntax RD for IMS Tables
More information can be found by reading Solution:
SOL1-BNY8R3 3270 Table Definer does not support RD (Raw Data) syntax for IMS tables
References:
==========
TIBCO® Object Service Broker Managing External Data Software Release 5.2.0
TIBCO® Object Service Broker Shareable Tools Software Release 5.2.0
TIBCO Service Gateway™ for IMS/DB Installing and Operating Software Release 5.2.0
IBM z/Architecture Principles of Operation
Description:
============
Using TIBCO® Object Service Broker (OSB) to process external data can provide many benefits but occasionally challenges. When dealing with Packed Data, OSB tables defined with "Packed Decimal" syntaxes such as L, O or P will fail if the external data in a row does not contain a valid packed value. Often legacy COBOL or IMS data will be initialized to low-values (binary zeroes) prior to loading. If a packed field is initialized to low-values, then conversion of the field to OSB packed format will fail.
This document will illustrate a technique for handling bad data, specifically packed data. This technique can be generalized to allow processing and cleansing of other syntaxes as well.
Environment:
============
* all environments where TIBCO Object Service Broker can be executed
* accessing legacy external data which might not contain valid data for the syntax used by OSB
Symptoms:
=========
When an attempt is made to convert invalid packed data, the following errors are typically observed:
* Unable to EDIT or BROWSE; Buffer conversion error for subview "tablename"
* S6BIM220E <tablename> <terminal> IMSROW: Conversion error: IMS to internal, field: "PACKED" Packed field contains non-numeric data.
Cause:
======
In the case of packed decimal data, the format of such data is strictly defined. Reference the IBM manual "z/Architecture Principles of Operation" section "Packed-Decimal Formats" for information on acceptable packed decimal formats. When packed data is manipulated by OSB, hardware instructions may be used to move or perform arithmetic operations on the data. If the data is not valid, these operations will fail and result in error messages being issued as previously documented.
Resolution:
===========
One method to deal with packed data conversion failures is to use the power of OSB Subview tables to map the data and allow a rule to process the field. Let us consider the case where we are processing data from a z/OS sequential dataset using an Import table and Subview along with a field Derivation Rule. A Derivation Rule allows execution of rule logic in processing the field data. Since this type of processing must happen in an Execution Environment (EE), how can conversion errors in a database gateway be handled? One answer is to use a special syntax of Raw Data (RD) for problematic packed fields and then define a Subview over the base table with a packed syntax and Derivation Rule. An example will illustrate the point.
Let us consider a case where we have a packed field which in most cases contains valid packed data but in a few rows, the data is invalid; perhaps, containing low-values, spaces or other uninitialized residual data. In rows that have valid packed data, we wish to process the data as found; but, in cases where the data is not valid, we only want to treat the field as if it were "NULL". NULL in OSB represents a field which has no data assigned to it. This is different than assigning a value of zero to the field which is a legitimate packed decimal numeric value.
Sample Definitions:
===================
To illustrate this technique, we need to create the following objects:
1) base IMP (IMPORT) table describing the input sequential file
2) Subview table to re-map the IMP data and allow a Derivation Rule to be executed
3) Derivation Rule
1) Define Base IMP Table. This table maps the problematic packed data field, called PACKED, to syntax Raw Data "RD" so that no conversion occurs while accessing the sequential dataset. In our example, the packed field exists in columns 1-4 of the sequential dataset. For illustrative purposes, the only other field is a string field, called COMMENT, containing comments. An IDGEN key has been defined to allow Browsing against the base IMP table but the virtual IDGEN key field does not actually exist in the IMP sequential dataset. The Table Definer definition would look like:
TABLE DEFINITION
Table: SOL1_BOXEWB_IMP Type: IMP Unit: EZL30 IDgen: Y
Source:
File: S6B.OSB52.IMPORT.DATA
DDname: External Routine Name:
Server ID:
Parameter Name Typ Syn Len Dec Class Src ' Event Rule Typ Acc
---------------- - -- --- -- - - ' ---------------- - -
_ LOCATION I C 16 0 L ' _
_ ' _
-------- IMP ---------|---------- Metadata Definition ------
Field Name Xsyn Xlen Xdec Offset Key Typ Syn Len Dec Ord Rqd Default
---------------- ---- ----- -- ----- - - -- ----- -- - - --------
_ IDGEN B 4 0 0 P I B 4 0
_ PACKED H 4 0 4 RD 9 0
_ COMMENT V 50 0 8 V 50 0
2) Define the Subview Table. The Subview definition redefines the problematic field(s) and provides a Derivation Rule that will be invoked when the associated field is accessed. The Derivation Rule will be able to analyze and provide the final value for the field.
The Table Definer definition would look like:
TABLE DEFINITION 0
Table: SOL1_BOXEWB_SUB Type: SUB Unit: EZL30 IDgen: N
Source: SOL1_BOXEWB_IMP
Select:
Lock Mode: D
Parameter Name Typ Syn Len Dec Class Src Source Name Default
---------------- - -- --- -- - - ---------------- ----------------
_ LOCATION 0 0 L
_
Synt Dec Order
Field Name Type Len Key Rqd Default Src Source Name
---------------- - -- ----- -- - - - ---------------- - ----------------
_ IDGEN I B 4 0 P
_ PACKED P 4 0 D SOL1_BOXEWB_RUL
_ COMMENT V 50 0
3) Define the Derivation Rule. The Derivation Rule is the location of the business rules that define how the packed data will be treated. The Derivation Rule makes the decisions on what value is extracted and presented to the user when accessing the IMP data. In our rule, we implement the business rules for the field PACKED. The business rules are:
* If the field PACKED contains valid packed data, return the valid data as the field value; otherwise, return the value "NULL" to indicate that the field does not contain valid data.
The Rule Definer definition could look like this:
RULE EDITOR ===> SCROLL: P
SOL1_BOXEWB_RUL;
_
_ ---------------------------------------------------------------------------
_ ------------------------------------------------------------+--------------
_ RETURN($TYPECAST('', 'P', 4, 0, FLDVAL(EVENTTABLE, ¦ 1
_ EVENTFIELD)) + 0); ¦
_ ---------------------------------------------------------------------------
_ ON ERROR :
_ RETURN(NULL);
The Derivation Rule is invoked when data is being assigned to the associated Subview field PACKED. The builtins EVENTTABLE and EVENTFIELD provide the name of the subview table and subview field being processed. Thus one Derivation Rule can be written to process many fields even in different tables. The $TYPECAST builtin is used coerce the Raw Data (RD) field PACKED to a packed format. If the field contains valid packed data, the coercion will succeed. If the field value is not a valid packed value, the coercion will fail and raise an "ON ERROR" condition which will cause the "ON ERROR" condition handler to be invoked and RETURN a value of NULL for the subview table field being processed.
4) Define the IMP input dataset to z/OS. For this example, a fixed block dataset called 'S6B.OSB52.IMPORT.DATA' was used. The physical characteristics of the dataset were:
Organization . . . : PS
Record format . . . : FB
Record length . . . : 80
Block size . . . . : 27920
and the data loaded into the dataset as displayed by TSO/ISPF browse was:
----+----1----+----2----+----3----+----4----+----5----+
REC#1: @This is a good 4 byte packed field: x'1234567C'
1357E88A48A48489984F48AA8498898848889874A7FFFFFFFC74444
246C3892092010766404028350713254069534A07D12345673D0000
-------------------------------------------------------
REC#2: q Another good 4 byte packed field: x'9876543C'
9753C99A889489984F48AA8498898848889874A7FFFFFFFC7444444
864C15638590766404028350713254069534A07D98765433D000000
-------------------------------------------------------
REC#3: [Ü[[This is a bad 4 byte packed field: x'BADBADBA'
BDABE88A48A4848884F48AA8498898848889874A7CCCCCCCC744444
ABDA389209201021404028350713254069534A07D21421421D00000
-------------------------------------------------------
----+----1----+----2----+----3----+----4----+----5----+
In the above data, the fields are defined as follows:
======== ========= ========= ===============
Beginning Ending
Field Column Column Description
======== ========= ========= ===============
IDGEN n/a n/a IDGEN is a virtual key field
PACKED 1 4 packed decimal. Third row has bad data
COMMENT 5 55 information concerning PACKED field
5) Now we can view the original contents of the base IMP table with the OSB Workbench. OSB converts the PACKED field data from syntax "RD" to a hexadecimal displayable value but no arithmetic operations could be performed against the PACKED field as defined with syntax "RD". The Browser shows that the third row contains invalid data for the field PACKED:
BROWSING TABLE : SOL1_BOXEWB_IMP
COMMAND ==>
SCROLL: P
IDGEN PACKED COMMENT
_ ----------- ---------- --------------------------------------------------
_ 1 1234567C This is a good 4 byte packed field: x'1234567C'
_ 2 9876543C Another good 4 byte packed field: x'9876543C'
_ 3 BADBADBA This is a bad 4 byte packed field: x'BADBADBA'
6) Browsing the Subview definition will show the effects of the Subview and Derivation Rule. We execute the rule STEBROWSE(SOL1_BOXEWB_SUB ) so as to avoid the Workbench changing the search path for our Derivation Rule. The results are:
BROWSING TABLE : SOL1_BOXEWB_SUB
COMMAND ==>
SCROLL: P
IDGEN PACKED COMMENT
_ ----------- -------- --------------------------------------------------
_ 1 1234567 This is a good 4 byte packed field: x'1234567C'
_ 2 9876543 Another good 4 byte packed field: x'9876543C'
_ 3 This is a bad 4 byte packed field: x'BADBADBA'
Browsing the SUB (Subview) table shows that the PACKED field value for the third row has now been set to NULL since the original IMP field did not contain a valid packed value. Contrast this with the data shown when browsing the base IMP table. Note also that the displayed packed field values are now integer numbers and not hexadecimal representations of the field value. The trailing sign nibble x'C' has also been removed and is reflected in the sign of the integer value.
Warning:
========
Customers using this technique with IMS should open a Service Request and ask to be notified when a fix is available for problem report:
OSBZ-1295 3270 Table Definer does not support Syntax RD for IMS Tables
More information can be found by reading Solution:
SOL1-BNY8R3 3270 Table Definer does not support RD (Raw Data) syntax for IMS tables
References:
==========
TIBCO® Object Service Broker Managing External Data Software Release 5.2.0
TIBCO® Object Service Broker Shareable Tools Software Release 5.2.0
TIBCO Service Gateway™ for IMS/DB Installing and Operating Software Release 5.2.0
IBM z/Architecture Principles of Operation
Issue/Introduction
Technique for Detecting and Handling Invalid Data via TIBCO® Object Service Broker
Feedback
Yes
No
Powered by
