Re: GENERATE_SQL doesn't like object_library = *LIBL

Hi Dan,

I'd built some SQL UDF function wrappers around IBM's GENERATE_SQL
procedure, all named M_GENERATE_SQL. The different specific versions of the
function accept more of less parameters (parameter overloading). For the
functions that accept fewer parameters, default values are applied for the
not supplied parameters.

One version of those functions requires only 3 parameters:
DATABASE_OBJECT_NAME, DATABASE_OBJECT_LIBRARY_NAME, DATABASE_OBJECT_TYPE

On V7R1, I tried *LIBL as follows, which did NOT work (job got into an
endless loop with job log spammed with cursor not open errors):

values M_GENERATE_SQL( 'MY_TABLE', '*LIBL', 'TABLE' )

This, however, did work, by dynamically finding the first schema containing
the file in the library list:

values M_GENERATE_SQL( 'MY_TABLE', ( select L.SCHEMA_NAME FROM
QSYS2.LIBRARY_LIST_INFO L inner join QSYS2.SYSTABLES T on T.TABLE_NAME =
'MY_TABLE' and T.TABLE_SCHEMA = L.SCHEMA_NAME order by L.ORDINAL_POSITION
fetch first row only ), 'TABLE' )

I've been doing a ton of generated SQL stuff via GENERATE_SQL, but hadn't
noticed the *LIBL issue, as I'd always been feeding it a schema name.

That SELECT query above would be a good candidate to put inside a scalar
function named something like TABLE_FIRST_LIBL_SCHEMA. Example of use:
values M_GENERATE_SQL( 'MY_TABLE', ( values TABLE_FIRST_LIBL_SCHEMA(
'MY_TABLE' ) ), 'TABLE' )

Hopefully this aids you in your task.

Mike

date: Thu, 17 Aug 2017 15:41:06 -0400

from: Dan <dan27649@xxxxxxxxx>
subject: Re: GENERATE_SQL doesn't like object_library = *LIBL

On Thu, Aug 17, 2017 at 3:29 PM, Musselman, Paul <
pmusselman@xxxxxxxxxxxxxxxx> wrote:

When creating an object (or a member) the system needs to know where you
want to put it. *LIBL is too generic!

This is the parameter that identifies the location of the existing table
object for which the DDL is being generated. And the v7r2 documentation
validates the use, but I'm guessing that v7r1 doesn't have the option to
use *LIBL here.

- Dan