Re: Insert and Long Names

On 28-Dec-2015 09:42 -0700, Alan Campin wrote:

We are doing data conversion of large amount of data and we are
seeing very interesting behavior if we are using long names when we
do the writes.

All the tables are defined using DDL using both long names and short.
If we write to the tables using long names we see CPU shot straight
up and running iDoctor reveals that each write seems to be making
function calls to an internal system API that translates the long
name to the short.

It would make sense to me that it would do this once but for every
record?

To write 50,000 records using long names takes 8 seconds and 2
seconds using short names. We have a very big machine.

Turn on Job Watcher and looked at CPU consumption. Huge spike
straight up, straight across and down for long names. Doesn’t even
register for short names.

Anybody else run into this?

We are V7R2.

Despite the volume of text, seemingly short on specifics What API [and what called that API]? What INSERT statement [and context; actual statement and pseudo-code might be helpful to explain the invocation]? What DDL used to CREATE TABLE [notably, what library]? Mimicking science, sharing the process used, would allow others to attempt to verify the same effect [not me, as I have no access to IBM i 7.2].

Even with repeated dynamic INSERT, I would expect a VALUES insert to create a reusable statement per Parameter Marker Conversion [I think I recall that is the name] unless an QAQQINI option disables that, and that the cursor would be maintained as pseudo-closed unless between each request something like the library list or DBF override(s) had changed. Even so, I would expect such a poorly-coded use of INSERT may still need to ask for the long file name resolution to the short name; i.e. the statement validation would seem suspect if the name is not ensured.? Given the SQL INSERT is dynamic, then that could be resolved [err, circumvented] easy enough by changing the statement to refer to the [non-QTEMP] name resolved by the invocation of the Retrieve Short Name (QDBRTVSN) API, if the name is known to be static.

FWiW there have been past defects with PTFs that directly impacted the Performance (PERFM) of the SQL, due to the "long name" resolution; e.g. APAR SE44980 and APAR SE45492, APAR SE54658. I found nothing for v7r2, nor anything newer than those.