Re: iNav Run SQL Scripts

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/25/2018
06:03:47 PM:

I assume you want to run logic like this?

begin
declare res_count int;
declare continue handler for sqlstate '42710' begin end;
create table qtemp.count_table (c int);
set res_count = (select count(*) from qsys2.systriggers);
if res_count > 100 then
insert into qtemp.count_table values res_count;
else
insert into qtemp.count_table values 999;
end if;
end;

It's been available since 2013 at 7.1 and up. Look in the SQL

Reference

for compound (dynamic) statement.

Sue Romano
Db2 for IBM i Development

Thanks. I got my compound SQL statement to work in iNav. However,
I'm not getting the expected results in my temporary table. It appears
that my SET SCHEMA statement is not working to point to the desired
library. Instead, it seems that the first table counted is the same count
I get for all the rest of the tables -- even though I know that is not
true. Do I have to create an ALIAS to the desired table in order to use
the same table name in different libraries?

Begin
---------------------------------------------------------------------------
-- variable definitions
---------------------------------------------------------------------------
Declare CompanyNumber Char(5);
Declare CompanyStatus Char(15);
Declare CompanyLibrary Char(10);
Declare RowCount BigInt;

Declare CurState Char(5);
Declare Str1 VarChar(3000);

Declare Company_Cursor Sensitive Cursor
for Select ORGANIZATION_KEY, STATUS, LIBRARY_NAME
From SHR460/CILCINFV03
Order by CIORGKY;

Declare Continue Handler for sqlstate '42710' begin end;

---------------------------------------------------------------------------
-- create temporary work table
---------------------------------------------------------------------------
Create Table QTEMP/DLC_TEMP_TABLE for system name DLCTEMPT
(
Company_Number char(5) not null
,Table_Name char(10)
,Row_Count bigint
);

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) > '01' Then
Set Str1 = 'Temporary table create failure.'
|| ' (' || CurState || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

Delete QTEMP/DLC_TEMP_TABLE;

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) > '02' Then
Set Str1 = 'Temporary table delete failure.'
|| ' (' || CurState || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

---------------------------------------------------------------------------
-- open the local company cursor
---------------------------------------------------------------------------
Open Company_Cursor;

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) <> '00' Then
Set Str1 = 'Company cursor open failure.'
|| ' (' || CurState || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

---------------------------------------------------------------------------
-- loop on local company information
---------------------------------------------------------------------------
Company_Loop:
Loop

Fetch Company_Cursor
into CompanyNumber, CompanyStatus, CompanyLibrary;

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) <> '00' Then
If SubStr(CurState,1,2) = '02' Then
Leave Company_Loop;
End If;
Set Str1 = 'Company cursor fetch failure.'
|| ' (' || CurState || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

IF CompanyStatus IN ('Active','Inactive')
And CompanyLibrary > ' ' Then
Set Schema = CompanyLibrary;
Select Count(*) into RowCount from IM02T;
Insert into QTEMP/DLC_TEMP_TABLE
Values(CompanyNumber, 'IM02T', RowCount);
End If;

Iterate Company_Loop;
End Loop;

---------------------------------------------------------------------------
-- close the local company cursor
---------------------------------------------------------------------------
Close Company_Cursor;

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) <> '00' Then
Set Str1 = 'Company cursor close failure.'
|| ' (' || CurState || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

---------------------------------------------------------------------------
-- commit the temporary table
---------------------------------------------------------------------------
Commit;

---------------------------------------------------------------------------
-- exit script
---------------------------------------------------------------------------
End;

Select *
From QTEMP/DLC_TEMP_TABLE
Order by Row_Count Desc, Company_Number;


Sincerely,

Dave Clark