Stored procedures truncate result

[dkornacki-cutco]

Originally I wrote the itoolkit for Python equivalent to use system API QESRSRVA to retrieve service attributes. I debugged to the point the the stored procedure is called and just directly called it.

There seems to be an issue where the maximum records returned from the iPLUG stored procedures is 4 of 3000 length each.

I have tested this with ODBC through itoolkit and JDBC through Run SQL Scripts utility. Drivers have been updated to current versions from ACS packages. I've tried both stateless and stateful (IPC) methods. User profile is setup with CCSID 37.

Here is the call:

call QXMLSERV.iPLUGR512K('*na', '*here *cdata', '<?xml version="1.0"?><xmlservice><pgm error="on" name="QESRSRVA" var="retrieveServiceAttrs"><parm io="both" var="p1"><ds len="receriverVarLen" var="receiverVar"><data type="10i0" var="numServiceAttrRetrieved"/><data dim="14" type="10i0" var="offsetsToServiceAttrTempls"/><ds var="serviceAttrTemplates"><ds var="key1template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="attr"/></ds></ds><ds var="key2template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="attr"/></ds></ds><ds var="key3template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="nameFormat"/><data type="17a" var="serviceProviderName"/></ds></ds><ds var="key4template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="10a" var="typeOfPTFInstall"/></ds></ds><ds var="key5template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data enddo="numOfEntries" type="10i0" var="numOfEntries"/><data dim="50" dou="numOfEntries" type="10a" var="userList"/></ds></ds><ds var="key6template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="attr"/></ds></ds><ds var="key7template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="attr"/></ds></ds><ds var="key8template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="10i0" var="attr"/></ds></ds><ds var="key9template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="10a" var="ecsMsgQueue"/><data type="10a" var="library"/></ds></ds><ds var="key10template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="30a" var="attr"/></ds></ds><ds var="key11template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="30a" var="attr"/></ds></ds><ds var="key12template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="30a" var="attr"/></ds></ds><ds var="key13template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data type="1a" var="attr"/></ds></ds><ds var="key14template"><data type="10i0" var="serviceAttrKey"/><data type="1a" var="dataTypeOfServiceAttr"/><data type="1a" var="statusOfServiceAttr"/><data type="2a" var="reserved"/><data type="10i0" var="lengthOfServiceAttr"/><ds var="serviceAttr"><data enddo="numOfEntries2" type="10i0" var="numOfEntries"/><data dim="20" dou="numOfEntries2" type="10a" var="ptfGroupList"/></ds></ds></ds></ds></parm><parm io="both" var="p2"><data setlen="receriverVarLen" type="10i0" var="lenOfReceiverVar"/></parm><parm io="both" var="p3"><data type="10i0" var="numOfServiceAttrKeys"><![CDATA[14]]></data></parm><parm io="both" var="p4"><ds var="serviceAttrKeys"><data type="10i0" var="key1"><![CDATA[1]]></data><data type="10i0" var="key2"><![CDATA[2]]></data><data type="10i0" var="key3"><![CDATA[3]]></data><data type="10i0" var="key4"><![CDATA[4]]></data><data type="10i0" var="key5"><![CDATA[5]]></data><data type="10i0" var="key6"><![CDATA[6]]></data><data type="10i0" var="key7"><![CDATA[7]]></data><data type="10i0" var="key8"><![CDATA[8]]></data><data type="10i0" var="key9"><![CDATA[9]]></data><data type="10i0" var="key10"><![CDATA[10]]></data><data type="10i0" var="key11"><![CDATA[11]]></data><data type="10i0" var="key12"><![CDATA[12]]></data><data type="10i0" var="key13"><![CDATA[13]]></data><data type="10i0" var="key14"><![CDATA[14]]></data></ds></parm><parm io="both" var="p5"><ds len="errorCodeLen" var="errorCode"><data type="10i0" var="bytesProvided"/><data type="10i0" var="bytesAvailable"/><data setlen="errorCodeLen" type="7a" var="exceptionID"/><data type="1a" var="reserved"/></ds></parm></pgm></xmlservice>');

For reference, here is the original itoolkit build:

            itool = iToolKit()
            itool.add(
                iPgm("retrieveServiceAttrs", "QESRSRVA", {"error": "on"})
                .addParm(
                    iDS("receiverVar", {"len": "receriverVarLen"}) # output
                    .addData(iData("numServiceAttrRetrieved", "10i0"))
                    .addData(
                        iData("offsetsToServiceAttrTempls", "10i0", iopt={"dim": "14"})
                    )
                    .addData(
                        iDS("serviceAttrTemplates")
                        .addData(
                            iDS("key1template") # Automatic Problem Analysis
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "1a"))
                            )
                        )
                        .addData(
                            iDS("key2template") # Automatic Problem Reporting
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "1a"))
                            )
                        )
                        .addData(
                            iDS("key3template") # Service Provider to Report Problem
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("nameFormat", "1a"))
                                .addData(iData("serviceProviderName", "17a"))
                            )
                        )
                        .addData(
                            iDS("key4template") # PTF Install Type
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("typeOfPTFInstall", "10a"))
                            )
                        )
                        .addData(
                            iDS("key5template") # Critical Message Recipients
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("numOfEntries", "10i0", iopt={"enddo": "numOfEntries"}))
                                .addData(iData("userList", "10a", iopt={"dim": "50", "dou": "numOfEntries"}))
                            )
                        )
                        .addData(
                            iDS("key6template") # Send Data Packets
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "1a"))
                            )
                        )
                        .addData(
                            iDS("key7template") # Copy PTFs
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "1a"))
                            )
                        )
                        .addData(
                            iDS("key8template") # PTF group levels
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "10i0"))
                            )
                        )
                        .addData(
                            iDS("key9template") # ECS message queue
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("ecsMsgQueue", "10a"))
                                .addData(iData("library", "10a"))
                            )
                        )
                        .addData(
                            iDS("key10template") # System-Disabled Reporting Connection Number
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "30a"))
                            )
                        )
                        .addData(
                            iDS("key11template") # System-Disabled Call-Back Connection Number
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "30a"))
                            )
                        )
                        .addData(
                            iDS("key12template") # Service Provider Connection Number
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "30a"))
                            )
                        )
                        .addData(
                            iDS("key13template") # Order PTFs automatically
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("attr", "1a"))
                            )
                        )
                        .addData(
                            iDS("key14template") # Order PTF groups
                            .addData(iData("serviceAttrKey", "10i0"))
                            .addData(iData("dataTypeOfServiceAttr", "1a"))
                            .addData(iData("statusOfServiceAttr", "1a"))
                            .addData(iData("reserved", "2a"))
                            .addData(iData("lengthOfServiceAttr", "10i0"))
                            .addData(
                                iDS("serviceAttr")
                                .addData(iData("numOfEntries", "10i0", iopt={"enddo": "numOfEntries2"}))
                                .addData(iData("ptfGroupList", "10a", iopt={"dim": "20", "dou": "numOfEntries2"}))
                            )
                        )
                    )
                )
                .addParm(iData("lenOfReceiverVar", "10i0", iopt={"setlen": "receriverVarLen"}))  # input
                .addParm(iData("numOfServiceAttrKeys", "10i0", "14"))  # input
                .addParm(
                    iDS("serviceAttrKeys") # input
                    .addData(iData("key1", "10i0", "1"))
                    .addData(iData("key2", "10i0", "2"))
                    .addData(iData("key3", "10i0", "3"))
                    .addData(iData("key4", "10i0", "4"))
                    .addData(iData("key5", "10i0", "5"))
                    .addData(iData("key6", "10i0", "6"))
                    .addData(iData("key7", "10i0", "7"))
                    .addData(iData("key8", "10i0", "8"))
                    .addData(iData("key9", "10i0", "9"))
                    .addData(iData("key10", "10i0", "10"))
                    .addData(iData("key11", "10i0", "11"))
                    .addData(iData("key12", "10i0", "12"))
                    .addData(iData("key13", "10i0", "13"))
                    .addData(iData("key14", "10i0", "14"))
                )
                .addParm(
                    iDS("errorCode", {"len": "errorCodeLen"}) # input/output
                    .addData(iData("bytesProvided", "10i0")) # input
                    .addData(iData("bytesAvailable", "10i0")) # output
                    .addData(iData("exceptionID", "7a", iopt={"setlen": "errorCodeLen"})) # output
                    .addData(iData("reserved", "1a")) # output
                    #.addData(iData("exceptionData", "char(*)")) # output: TODO
                )
            )

Edit: It seems that the parameters do not align correctly so I will try to remap lengths appropriately (though RPG style from C style conversions seem to be correct after multiple takes). However, I believe there may still be an indirect bug. Just taking a wild guess but there may be some kind of overflow issue where more checking of sizes needs to happen. I may take a crack at debugging the RPG at some point.

[jimoibm]

What was the error you got in the stored procedure call?

[alanseiden]

@dkornacki-cutco It seems you are reading uninitialized memory from after the API return data ends. I think this particular API is too dynamic and complex to access using XMLSERVICE directly for your needs. I'd suggest writing an RPG program that calls the API, then calling the RPG program from XMLSERVICE.
The end of your output looks like this:

[kadler]

Yeah, this is one of the shortcomings of XMLSERVICE. It works very well for scalar and record-oriented data, but variable length unstructured data is harder for it to deal with because it is declarative not imperative. XMLSERVICE has no way to declare something like "there's a blob of data and at this offset contains a number of keys and at this offset is an array whose length is the previous value I just mentioned and each of these array entries contains an offset to a template header; loop over each of these template headers and if the template header field says it's this kind of data, do this but if it's this kind of data do that..." It would probably be easier to just say return the whole blob of data in binary and then use Python's built-in EBCDIC support along with struct to read the data out. Of course XML doesn't play nicely with binary data, so you'd need to set hex=on to return the data hex-encoded (base64 would be way more efficient, but it's what we've got).

I'm sure if Tony were still working on it, he could give you some kind of way to do this, but I suspect it'd be brittle and rely on the offsets being fixed (which you should never assume) and using the "hole" type to ignore gaps in the output structure.