KB8952: What are the options provided by the VLDB property, Commit Level, in MicroStrategy Engine

The VLDB property, Commit Level, is located in the Tables folder. This setting provides options for issuing the 'commit' statement for intermediate tables during SQL generation, as follows:
 

1. No Commit - No commit statement is issued.
2. Post DDL - A commit statement is issued after a Create statement.
3. Post DML - A commit statement is issued after an Insert/Select statement.
4. Post DDL and DML - A commit statement is issued after both Create and Insert/Select statements.

The default value is No Commit. The acronyms DDL and DML stand for Data Definition Language and Data Manipulation Language, respectively.
 
These options are illustrated on the following example SQL which contains 2 create and 2 insert statements: 

1. No Commit
    
   create table #ZZMD00( CALL_CTR_ID TINYINT, WJXBFS1 FLOAT)
   
   insert into #ZZMD00
   select a12.CALL_CTR_ID CALL_CTR_ID, (1.0 * count(a11.ORDER_ID)) WJXBFS1
   from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
   where a11.SHIP_DATE < (a11.ORDER_DATE + 7)
   group by a12.CALL_CTR_ID
   
   create table #ZZMD01( CALL_CTR_ID TINYINT, WJXBFS1 INTEGER)
   
   insert into #ZZMD01
   select a12.CALL_CTR_ID CALL_CTR_ID, count(a11.ORDER_ID) WJXBFS1
   from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
   group by a12.CALL_CTR_ID
   

• Post DDL
   
  create table #ZZMD00( CALL_CTR_ID TINYINT, WJXBFS1 FLOAT)
  
  commit
  
  insert into #ZZMD00
  select a12.CALL_CTR_ID CALL_CTR_ID, (1.0 * count(a11.ORDER_ID)) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  where a11.SHIP_DATE < (a11.ORDER_DATE + 7)
  group by a12.CALL_CTR_ID
  
  create table #ZZMD01( CALL_CTR_ID TINYINT, WJXBFS1 INTEGER)
  
  commit
  
  insert into #ZZMD01
  select a12.CALL_CTR_ID CALL_CTR_ID, count(a11.ORDER_ID) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  group by a12.CALL_CTR_ID
  

 
NOTE:
The effects of the commit statement may be overridden if autocommit is turned 'on' at the ODBC driver level. In that case, the SQL passes will be committed regardless of whether a commit statement (as dictated by the Commit Level property) is issued or not.
 

• Post DML
   
  create table #ZZMD00( CALL_CTR_ID TINYINT, WJXBFS1 FLOAT)
  
  insert into #ZZMD00
  select a12.CALL_CTR_ID CALL_CTR_ID, (1.0 * count(a11.ORDER_ID)) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  where a11.SHIP_DATE < (a11.ORDER_DATE + 7)
  group by a12.CALL_CTR_ID
  
  commit
  
  create table #ZZMD01( CALL_CTR_ID TINYINT, WJXBFS1 INTEGER)
  
  insert into #ZZMD01
  select a12.CALL_CTR_ID CALL_CTR_ID, count(a11.ORDER_ID) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  group by a12.CALL_CTR_ID
  
  commit
  
• Post DDL and DML
   
  create table #ZZMD00( CALL_CTR_ID TINYINT, WJXBFS1 FLOAT)
  
  commit
  
  insert into #ZZMD00
  select a12.CALL_CTR_ID CALL_CTR_ID, (1.0 * count(a11.ORDER_ID)) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  where a11.SHIP_DATE < (a11.ORDER_DATE + 7)
  group by a12.CALL_CTR_ID
  
  commit
  
  create table #ZZMD01( CALL_CTR_ID TINYINT, WJXBFS1 INTEGER)
  
  commit
  
  insert into #ZZMD01
  select a12.CALL_CTR_ID CALL_CTR_ID, count(a11.ORDER_ID) WJXBFS1
  from ORDER_FACT a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
  group by a12.CALL_CTR_ID
  
  commit