db2 "NOT LOGGED INITIALLY" not working

My DB2 version is LUW v11.1.

I am running select queries on big tables and insert to new tables, so I try to use "NOT LOGGED INITIALLY" when creating new tables to avoid generating large size of log. But it seems that the NLI option is not working.

The following is my sql code:

create table diabetes_v3_2.comm_outpatient_prescription_drugs_t2dm
    as (select * from commercial.outpatient_prescription_drugs)
    with no data
    not logged initially;
insert into diabetes_v3_2.comm_outpatient_prescription_drugs_t2dm
    select * from commercial.outpatient_prescription_drugs
        where enrolid in (
            select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment
        );


create table diabetes_v3_2.comm_outpatient_services_t2dm
    as (select * from commercial.outpatient_services)
    with no data
    not logged initially;
insert into diabetes_v3_2.comm_outpatient_services_t2dm
    select * from commercial.outpatient_services
        where enrolid in (
            select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment
        );

I run the script as db2 -tvf script.sql. But I still got the "SQL0964C The transaction log for the database is full" error:

/* Generate cohort data for the cohort after *  filtering according to age and continuous * enrollment criteria. */ /* facility header */ /* inpatient admissions */ /* inpatient services */ /* outpatient prescription drugs */ create table diabetes_v3_2.comm_outpatient_prescription_drugs_t2dm as (select * from commercial.outpatient_prescription_drugs) with no data not logged initially
DB20000I  The SQL command completed successfully.

insert into diabetes_v3_2.comm_outpatient_prescription_drugs_t2dm select * from commercial.outpatient_prescription_drugs where enrolid in ( select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment )
  Number of rows affected : 275423901
DB20000I  The SQL command completed successfully.

/* outpatient services */ create table diabetes_v3_2.comm_outpatient_services_t2dm as (select * from commercial.outpatient_services) with no data not logged initially
DB20000I  The SQL command completed successfully.

insert into diabetes_v3_2.comm_outpatient_services_t2dm select * from commercial.outpatient_services where enrolid in ( select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment )
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

Why is this?

Solution

To use NOT LOGGED INITIALLY properly, the application doing the changes should NOT have AUTOCOMMIT enabled. Having AUTOCOMMIT OFF also helps define the scope of the transactions:

For CLP, you can turn AUTOCOMMIT OFF using the environment variable DB2OPTIONS :
```
export DB2OPTIONS=+c
```
If you execute a script containing update SQL statements, such as inserts, and DB2OPTIONS is not set, you can execute the script using:
```
db2 +c -tvf input_script.sql  -z output_script.out
```

Make sure you add explicit COMMIT statements in your scripts to ensure that they occur at reasonable points.