Use of short table names in SQL

[davedavedave]

Hello All:

Came across a problem and am wondering if anyone may have seen this before. I have a multiple pass query and the first pass is as follows:

create volatile table ZZTBE050ENHMQ000, no fallback, no log(
Some_Thing SMALLINT)
primary index (Some_Thing) on commit preserve rows


;insert into ZZTBE050ENHMQ000
select distinct a11.Some_Thing Some_Thing
from Some_Table a11
where a11.some_condition in ('Y')

Later in the query, when another pass is attempting to going on pass 1, pass 1's name has been shortened to ZZMQ00.

All other tables in this query use the short name for the volatile tables except for the first pass - anyone seen this before?

 

[entaroadun]

This is standard MSTR behavior. MSTR generates SQL passes with short temp table names. When the SQL Engine actually starts executing a pass, the SQL is updated with a long name. This is because the short name is generic and common across different report instances.

The only time you see a discrepancy is if you view the SQL from the job monitor while the report is running. Passes already executed or currently executing have the long name. Passes to be executed have the short name.

If you view the SQL from the report object itself without first running the report, you will see all passes with the short names.

If you view the SQL from the report object AFTER execution, you will see all passes with the long names.

The short name is useful, since you can see clearly the intention of the pass by its name. ZZMD are regular passes. ZZMQ are set qualifications (I believe). The documentation explains this fully.