Persistence ESQL API

The Persistence API, otherwise known as the multi row insert API, provides a set of helper functions that can be used to prepare and insert data into the database.

Table 1. EndMapper (v2) ESQL API
API Function	Description
`CREATE PROCEDURE InitPersist(INOUT rInEnvOutPersist REFERENCE);`	Initializes the basic place holder and moves the reference to point to it
`CREATE PROCEDURE DisablePsuedoIDs(IN rPersist REFERENCE);`	By default, the API uses pseudo IDs for objects that may not be persisted to the database. Invoking this function switches the API to use database allocated IDs for all objects. At present, there are no known reasons to use this function, so it is recommended it not be used unless suggested by IBM®.
`CREATE PROCEDURE InitPersistSet(IN rPersist REFERENCE, OUT rDataSet REFERENCE, IN cDataType CHAR, IN cSubDataType CHAR, IN cInsertObject CHAR);`	Create an area for the given Type/Subtype and store the basic insert view to be used: cDataType = PT \| FRAG \| BAT \| TXN \| OV \| OOR \| ERR \| MISC cSubDataType = application defined value - allows to differentiate between 2 inserts of the same type For example, PAY_TXN and TXN - where each has a different INSERT statement
`CREATE PROCEDURE SetApplicationRowPerInsertLimit(IN rPersist REFERENCE, IN cDataType CHAR, IN cSubDataType CHAR, IN nRowsPerInsert INTEGER);`	Specify the maximum number of rows to be inserted per INSERT statement for this data set. Value table configuration properties set the upper limit.
`CREATE PROCEDURE SetTxnRouteInfo(IN rPersist REFERENCE, IN cDataType CHAR, IN cSubDataType CHAR, IN cObjClass CHAR, IN cTxnType CHAR, IN cSvcPartRank CHAR, IN nSvcPartId INTEGER, IN cCacheId CHAR);`	Depending on channel settings and where they route, some outbound transactions may not be persisted. Allow the Action to provide routing information - to allow the API to look ahead to see where the transaction will route.
`CREATE PROCEDURE RelateObj1ToObj2(IN rPersist REFERENCE, IN nId1 INTEGER, IN nId2 INTEGER, IN cType CHAR);`	Create an OBJ_OBJ_REL record of the specified type between the two specified objects.
`CREATE PROCEDURE InitGenericRow(IN rPersist REFERENCE, IN rDataSet REFERENCE, INOUT rRow REFERENCE);`	Initialize a new row for the next set of data. All this does is increment the row count, test it against the limit, and create new row set if the limit is exceeded
`CREATE PROCEDURE InitRowFromTree(IN rPersist REFERENCE, IN rDataSet REFERENCE, IN rInRow REFERENCE);`	Add a row to the persistence cache. The data for the row is the child elements of the reference 'rInRow'. Must contain a child element 'ID'.
`CREATE PROCEDURE InitTxnRowFromTree(IN rPersist REFERENCE, IN rDataSet REFERENCE, IN rInRow REFERENCE);`	Add a transaction row to the persistence cache. The data for the row is the child elements of the reference 'rInRow'. Must contain a child element 'ID'. Special handling of ISF_DATA column is provided for z/OS®.
`CREATE FUNCTION InitObjectRow(IN rPersist REFERENCE, IN rDataSet REFERENCE, INOUT rRow REFERENCE) RETURNS INTEGER;`	Add a row to the persistence cache. The data for the row is the child elements of the reference 'rInRow'. Should not contain a child element 'ID'. ID is generated internally.
`CREATE PROCEDURE SetColValChar(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN cValue CHAR);`	Add a string value to the current cached row.
`CREATE PROCEDURE SetColValInt(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN nValue INTEGER);`	Add an integer value to the current cached row.
`CREATE PROCEDURE SetColValDec(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN dValue DECIMAL);`	Add a decimal value to the current cached row.
`CREATE PROCEDURE SetColValDate(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN dtValue DATE);`	Add a date value to the current cached row.
`CREATE PROCEDURE SetColValTime(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN tValue TIME);`	Add a time value to the current cached row.
`CREATE PROCEDURE SetColValTimeStamp(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN tsValue TIMESTAMP);`	Add a time stamp value to the current cached row. For more information relating to persistence and the database time zone, see Persistence & database time zone.
`CREATE PROCEDURE SetTxnStatus(IN rData REFERENCE, IN rRow REFERENCE, IN cStatus CHAR);`	Set the STATUS value for the current cached row. Supports alteration of the status value for efficiency.
`CREATE PROCEDURE SetColValBLOB(IN rData REFERENCE, IN rRow REFERENCE, IN cColName CHAR, IN rParent REFERENCE, IN cFieldName CHAR);`	Add a BLOB value to the current cached row.
`CREATE PROCEDURE SetTxnISFBLOB(IN rData REFERENCE, IN rRow REFERENCE, IN nId INTEGER, IN rParent REFERENCE, IN cFieldName CHAR);`	Add a BLOB value representing ISF to the current cached row. Automatically handles z/OS length restrictions.
`CREATE PROCEDURE SetTxnRawData(IN rData REFERENCE, IN rRow REFERENCE, IN nId INTEGER, IN rParent REFERENCE, IN cFieldName CHAR);`	Add a BLOB value representing TRANSACTION_BASE.RAW_DATA to the current cached row.
`CREATE PROCEDURE SetPtRawData(IN rData REFERENCE, IN rRow REFERENCE, IN nId INTEGER, IN rParent REFERENCE, IN cFieldName CHAR);`	Add a BLOB value representing TRANSMISSION_BASE.DATA to the current cached row. Automatically handles z/OS length restrictions.
`CREATE PROCEDURE SetFragRawData(IN rData REFERENCE, IN rRow REFERENCE, IN nId INTEGER, IN rParent REFERENCE, IN cFieldName CHAR);`	Add a BLOB value representing FRAGMENT_BASE.DATA to the current cached row.
`CREATE PROCEDURE InsertSmallObjectValue(IN rPersist REFERENCE, IN nObjID INTEGER, IN cCat CHAR, IN cKey CHAR, IN cVal CHAR);`	Insert an OBJ_VALUE record related to the specified object. Set the SMALL_VALUE for category/key.
`CREATE PROCEDURE InsertFullObjectValue(IN rPersist REFERENCE, IN nObjID INTEGER, IN cCat CHAR, IN cKey CHAR, IN rTree REFERENCE);`	Insert an OBJ_VALUE record related to the specified object. Set the LOB_VALUE for category/key.
`CREATE PROCEDURE InsertCounter(IN rPersist REFERENCE, IN nObjID INTEGER, IN cType CHAR, IN cName CHAR, IN cCount INTEGER, IN dAmount DECIMAL);`	Insert a COUNTER record related to the specified object. Set the count, the amount, or both, for type/name.