SYMPTOM:
Consider a scenario where an administrator wants log information about intermediate tables created during report execution for troubleshooting or statistical purposes independently from Strategy Enterprise Manager.
The administrator decides to insert information about the table into the temp_table_names table and decides to utilize the 'Table Pre Statement 1' VLDB property in the Pre/Post Statements folder. The following syntax is used:
insert into temp_table_names values ('???')
When the report is viewed in the 'View SQL' mode, the following SQL is generated (the SQL sample below only shows the first few passes of the report using Oracle syntax):
insert into temp_table_names values ('ZZMD00')
create table ZZMD00 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12
where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (1)
group by a12.YEAR_ID,
a11.REGION_ID
insert into temp_table_names values ('ZZMD01')
create table ZZMD01 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12
where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (2)
group by a12.YEAR_ID,
a11.REGION_ID
insert into temp_table_names values ('ZZOJ02')
create table ZZOJ02 nologging as
select pa1.YEAR_ID YEAR_ID,
pa1.REGION_ID REGION_ID
from ZZMD00 pa1
However, when the report is executing, the following SQL is actually passed to the warehouse:
Pass1 - Duration: 0:00:00.01
insert into temp_table_names values ('Dummy Name')
Pass2 - Duration: 0:00:00.04
create table ZZTP001003NMD000 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12
where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (1)
group by a12.YEAR_ID,
a11.REGION_ID
Pass3 - Duration: 0:00:00.03
insert into temp_table_names values ('')
Pass4 - Duration: 0:00:00.04
create table ZZTP001003NMD001 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12
where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (2)
group by a12.YEAR_ID,
a11.REGION_ID
Pass5 - Duration: 0:00:00.01
insert into temp_table_names values ('')
Pass6 - Duration: 0:00:00.04
create table ZZTP001003NOJ002 nologging as
select pa1.YEAR_ID YEAR_ID,
pa1.REGION_ID REGION_ID
from ZZTP001003NMD000 pa1
The first wildcard is replaced by the 'Dummy value', and all consecutive wildcards hold an empty string.
CAUSE:
The intermediate table name is resolved at the time of the table creation in the 'create table' or 'insert into' pass of SQL and is different than the name used in 'View SQL' mode. The name that is displayed during `View SQL` phase is a dummy name. The actual table name is not 6 characters long; but rather 16 characters long and it contains the information about the table creation timestamp.
The Strategy SQL Engine does not know the intermediate table name at the time the 'Table Pre Statement 1' is executed and cannot insert the correct value into the temp_table_names table. Instead, the 'Dummy value' and empty strings are inserted.
ACTION:
Place 'insert into temp_table_names values ('???')' into the 'Table Post Statement 1' VLDB property. Using the example above, the following SQL will be generated during report execution:
Pass0 - Duration: 0:00:00.04
create table ZZTP000LUTTMD000 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1 from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12 where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (1) group by a12.YEAR_ID,
a11.REGION_ID
Pass1 - Duration: 0:00:00.01
insert into temp_table_names values ('ZZTP000LUTTMD000')
Pass2 - Duration: 0:00:00.04
create table ZZTP000LUTTMD001 nologging as
select a12.YEAR_ID YEAR_ID,
a11.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1 from STATE_REGION_MNTH_SLS a11,
LU_MONTH a12 where a11.MONTH_ID = a12.MONTH_ID
and a11.REGION_ID in (2) group by a12.YEAR_ID,
a11.REGION_ID
Pass3 - Duration: 0:00:00.03
insert into temp_table_names values ('ZZTP000LUTTMD001')
Pass4 - Duration: 0:00:00.03
create table ZZTP000LUTTOJ002 nologging as
select pa1.YEAR_ID YEAR_ID,
pa1.REGION_ID REGION_ID from ZZTP000LUTTMD000 pa1
Pass5 - Duration: 0:00:00.01
insert into ZZTP000LUTTOJ002
select pa2.YEAR_ID YEAR_ID,
pa2.REGION_ID REGION_ID from ZZTP000LUTTMD001 pa2
Pass6 - Duration: 0:00:00.03
insert into temp_table_names values ('ZZTP000LUTTOJ002')
