1. Forum
  2. Usenet
  3. COMP.SYS.IBM.AS400.MISC

  • declare global temporary table

    From Marco Franchini@21:1/5 to All on Fri Jun 14 03:35:39 2019
    I created a script in which I create a stored procedure in which I use an instruction
    CREATE GLOBAL TEMPORARY TABLE ...
    and then I perform operations on the tables

    I use RUNSQLSTM command to execute script:
    if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
    if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

    is there a way in which I don't have to create the temporary table first ?

    Thanks

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Buck@21:1/5 to Marco Franchini on Fri Jun 14 11:03:56 2019
    On Friday, June 14, 2019 at 6:35:42 AM UTC-4, Marco Franchini wrote:
    I created a script in which I create a stored procedure in which I use an instruction
    CREATE GLOBAL TEMPORARY TABLE ...
    and then I perform operations on the tables

    I use RUNSQLSTM command to execute script:
    if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
    if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

    is there a way in which I don't have to create the temporary table first ?

    Try setting the ERRLVL parameter of RUNSQLSTM higher?
    --buck

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Marco Franchini@21:1/5 to All on Mon Jun 17 08:50:15 2019
    I use ERRLVL=30



    Try setting the ERRLVL parameter of RUNSQLSTM higher?
    --buck

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to All on Wed Jun 19 06:11:32 2019
    On Friday, 14 June 2019 11:35:42 UTC+1, Marco Franchini wrote:

    Hi

    in abstract, a global temporary table is nothing more than a subset of rows copied against a new table in qtemp. Saying that, you may want to keep your current solution using temporary tables, but taking advantage of the "dynamic sql". Here's an example
    taken from one of our procedures:

    DECLARE STMT CHAR ( 2000 ) ;
    DECLARE APX CHAR ( 1 ) DEFAULT '''' ;

    SET STMT =
    'declare global temporary table w_scan_ser# as (
    select distinct c.table_name, c.column_name
    from qsys2.syscolumns c join qsys2.systables t
    on (t.table_name=c.table_name and t.table_schema=c.table_schema and t.table_type=' || APX || 'P' || APX ||
    ') where c.table_schema=' || APX || PLIB || APX || ' and trim(c.column_name) like ' || APX || '%SER#' || APX ||
    ') with data with replace' ;

    PREPARE DCLSTMT FROM STMT ;
    EXECUTE DCLSTMT ;

    using dynamic sql the system will not check whether or not the temporary table exists.

    hope this helps

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to All on Wed Jun 19 05:42:43 2019
    Hi
    when u use the temporary table did you address it as session.mytemptab, qtemp.mytemptab or just using library list?

    e.g. I just ranit against ASC SQL client and it worked.

    declare global temporary table MyTemp as (
    select * from prodfil.f56232 fetch first 10 rows only) with data with replace;

    update qtemp.mytemp set xddoco=xddoco;
    update session.mytemp set xddoco=xddoco;

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jonathan Ball@21:1/5 to Marco Franchini on Wed Jun 19 13:07:47 2019
    On 6/14/2019 3:35 AM, Marco Franchini wrote:
    I created a script in which I create a stored procedure in which I use an instruction
    CREATE GLOBAL TEMPORARY TABLE ...
    and then I perform operations on the tables

    I use RUNSQLSTM command to execute script:
    if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
    if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

    is there a way in which I don't have to create the temporary table first ?

    Thanks

    I run into the same problem. You can work around this by adding a step to
    the RUNSQLSTM source to create the temporary table first (you can have
    multiple statements in a RUNSQLSTM source member.)

    Source:

    -- Stmt 1
    declare global temporary table my_table
    [table definition statement];

    -- Stmt 2
    create or replace procedure myschema.myprocedure
    [procedure definition statement(s)]

    If this is submitted to batch, you won't need to worry about the temporary table already existing. If you issue RUNSQLSTM interactively multiple
    times, you could handle the possibility of the temporary table already
    existing by something like this:

    -- Stmt 1
    begin
    declare continue handler for sqlstate '42710'
    truncate qtemp.my_table;
    declare global temporary table my_table
    [table definition statement];
    end;

    SQLSTATE 42710 is issued if you try to create an object that already
    exists. If the temporary table exists, just clear it (TRUNCATE). The
    compound statement will then end, and proceed on to Stmt 2. An alternative would be to switch from DECLARE GLOBAL TEMPORARY TABLE to CREATE OR REPLACE TABLE, which would allow you to dispense with the compound statement:

    -- Stmt 1
    create or replace table qtemp.my_table
    [table definition statement]
    on replace
    delete rows;

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)