DB2 EEE autoload fails due to insufficient storage (SQL3011C)

[CompGeek]

Hi there,

I am trying to load a file with 1729 records into a DB2 UDB EEE 7.1
DataBase (on AIX 4.3) which is across two partitions. The partitioning
key is BI_INSTITUTIONA L_KEY which is a BIGINT and is explicitly
defined in the table DDL (with "using hashing" ON). The autoloader
config file is attached below.

The results of the autoloader are as :

--------- START OF AUTOLOAD OUTPUT ---------

Utility program: "db2atld". Version: "07026".
Start reading autoloader configuration file: autoloader.cfg.
Finish reading autoloader configuration file: autoloader.cfg.
Start initializing autoloader process.
Finish initializing autoloader process.
The AutoLoader is now issuing all LOAD requests.
The LOAD operation has begun on partition "1".
The LOAD operation has begun on partition "0".
SQL3011C There is not enough storage to process the command.
The AutoLoader is now issuing all split requests.
Start db2split on node "1" in background.
The AutoLoader is waiting for all splitters to complete.
The utility has completed reading "1" megabytes from the user data.
The AutoLoader is waiting for all LOAD operations to complete.
The remote execution of the splitter utility on partition "1"
finished with remote execution code "0".

Operation Node SQL Code Result
_______________ _______________ _______________ _______________ _______________ ___
LOAD 000 +00003107 Success.
_______________ _______________ _______________ _______________ _______________ ___
LOAD 001 -00003011 Nothing loaded. Table normal.
_______________ _______________ _______________ _______________ _______________ ___
SPLIT 001 +00000000 Success.
_______________ _______________ _______________ _______________ _______________ ___
PRE-SPLIT 000 +00000000 Success.
_______________ _______________ _______________ _______________ _______________ ___
RESULTS: 1 of 2 LOADs completed successfully.
_______________ _______________ _______________ _______________ _______________ ___
Rows Read 885
Rows Skipped 0
Rows Loaded 885
Rows Rejected 0
Rows Deleted 0
Rows Committed 885

--------- END OF AUTOLOAD OUTPUT ---------
As shown, only one LOAD succeeds by loading 885 rows.

The current settings of the HEAP parameters for the DB are as :

Database heap (4KB) (DBHEAP) = 20000
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 20000
Max appl. control heap size (4KB) (APP_CTL_HEAP_S Z) = 5000
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 32768
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Could someone please help me with the correct settings.

Any advice/pointers to web forum URLs will be most welcome.

Thanx.

NP.


--------- Start of autoloader.cfg ---------
############### ############### ############### ########
# Release level of this configuration file.
# Please do not delete or modify this line.
RELEASE=V5.01

db2 LOAD FROM ff_bad_institut ional.out OF DEL MODIFIED BY KEEPBLANKS
COLDEL| SAVECOUNT 20 MESSAGES BadInstLoad.msg INSERT INTO
HCSRODS.TB_BAD_ INSTITUTIONAL(B I_INSTITUTIONAL _KEY,BI_HEADER_ KEY,BI_ERROR_CO UNT,BI_RECORD_T YPE,BI_FILING_D ATE,BI_FILING_S TATE_OR_CNTRY_C ODE,BI_SEQUENCE _NUMBER,BI_TIME ,BI_HCSR_SUFFIX ,BI_PROGRAM_IND ICATOR,BI_HCSR_ PTC_DATE,BI_DAT E_ADJUSTMENT_ID ENTIFIED,BI_SPO NSOR_SSN,BI_SPO NSOR_PAY_GRADE, BI_SPONSOR_BRAN CH_OF_SERVICE,B I_SPONSOR_STATU S,BI_PATIENT_RE LATION_TO_SPONS OR,BI_PATIENT_N AME,BI_PATIENT_ SSN,BI_PAT
ENT_DOB,BI_DEER S_DEPENDENT_SUF FIX,BI_PATIENT_ SEX,BI_PATIENT_ ZIP_CODE,BI_ENR OLLMENT_STATUS, BI_NAS_NUMBER,B I_REASON_FOR_PM NT_REDUCTION,BI _AMOUNT_BILLED, BI_AMOUNT_ALLOW ED,BI_AMOUNT_PA ID_BY_OHI,BI_AM OUNT_ALLOWED_BY _OHI,BI_AMT_OF_ THIRDPARTY_LIAB ILITY,BI_AMOUNT _OF_PAYMENT_RED UCTION,BI_PATIE NT_COINSURANCE, BI_PATIENT_COPA YMENT,BI_AMT_PA ID_BY_GOVT_CONT RACTOR,BI_OVERR IDE_CODE,BI_TYP E_OF_SUBMISSION ,BI_NAS_EX
EPTION_REASON,B I_HEALTH_CARE_P LAN_CODE,BI_DIA G_EDITION_IDENT IFIER,BI_REASON _FOR_ADJUSTMENT ,BI_SPECIAL_PRO CESSING_CODE,BI _SPECIAL_RATE_C ODE,BI_MAJOR_DI AGNOSTIC_CATEGO RY,BI_REASON_FO R_ISSUANCE,BI_C LAIM_FORM_TYPE, BI_PCM_LOCATION _DMIS_ID_CODE,B I_NBR_OF_PMNT_R EDCN_DAYS_SVCS, BI_PROV_CONTRAC T_AFFLN_CODE,BI _PROV_STATE_OR_ CNTRY_CODE,BI_P ROVIDER_TAXPAYE R_NUMBER,BI_PRO VIDER_SUB_IDENT IFIER,BI_PROVID ER_ZIP_COD
,BI_PROVIDER_PA RTICIPATION_IND ,BI_TYPE_OF_INS TITUTION,BI_ADM ISSION_DATE,BI_ BILL_CLASSIFICA TION_CODE,BI_FR EQUENCY_CODE,BI _TYPE_OF_ADMISS ION,BI_SOURCE_O F_ADMISSION,BI_ DISCHARGE_STATU S,BI_BEGIN_DATE _OF_CARE,BI_END _DATE_OF_CARE,B I_NUMBER_OF_BIR THS,BI_TOTAL_BE D_DAYS,BI_GOVT_ AUTHORIZED_BED_ DAYS,BI_ADMISSI ON_DIAGNOSIS,BI _PRINCIPAL_TREA TMENT_DIAG,BI_S ECONDARY_TREATM ENT_DIAG_1,BI_S ECONDARY_TREATM ENT_DIAG_2
BI_SECONDARY_TR EATMENT_DIAG_3, BI_SECONDARY_TR EATMENT_DIAG_4, BI_SECONDARY_TR EATMENT_DIAG_5, BI_SECONDARY_TR EATMENT_DIAG_6, BI_SECONDARY_TR EATMENT_DIAG_7, BI_SECONDARY_TR EATMENT_DIAG_8, BI_PRINCIPAL_OP NSP_CODE,BI_SEC ONDARY_OPNSP_CO DE_1,BI_SECONDA RY_OPNSP_CODE_2 ,BI_SECONDARY_O PNSP_CODE_3,BI_ SECONDARY_OPNSP _CODE_4,BI_SECO NDARY_OPNSP_COD E_5,BI_DRG_NUMB ER,BI_DRG_GROUP ER_EDITION,BI_D RG_PRICER_EDITI ON,BI_REV_
ATA_OCCURRENCE_ COUNT,BI_D_TYPE _OF_SBMSN_SORT_ NUMBER,BI_D_REC ORD_RECVD_SEQ_N UMBER,BI_D_PATI ENT_PAY_TOTAL,B I_D_MTF_CODE,BI _D_PATIENT_AGE, BI_D_CYCLE_NUMB ER,BI_D_BENEFIT _CLAIM_COUNT_CO DE,BI_D_HOSP_DE PARTMENT_NUMBER ,BI_D_CARE_END_ FISCAL_YEAR,BI_ D_HEALTH_SVCS_R EGION_CODE,BI_D _BENEFICIARY_CA TEGORY,BI_D_ADM ISSION_COUNT_CO DE,BI_D_CATEGOR Y_OF_CARE,BI_D_ DRG_DERIVED_COD E,BI_ERROR_STRI NG,BI_S_AMOUNT_ BILLED,BI_
_AMOUNT_ALLOWED ,BI_S_AMOUNT_PA ID_BY_OHI,BI_S_ AMOUNT_ALLOWED_ BY_OHI,BI_S_AMT _OF_THIRDPARTY_ LIAB,BI_S_AMT_O F_PAYMENT_REDUC TION,BI_S_PATIE NT_COINSURANCE, BI_S_PATIENT_CO PAYMENT,BI_S_AM T_PAID_BY_GOVT_ CONTCTR,BI_S_NB R_PMNT_REDCN_DA YS_SVCS,BI_S_NU MBER_OF_BIRTHS, BI_S_TOTAL_BED_ DAYS,BI_S_GOVT_ AUTHORIZED_BED_ DAYS)
DATA BUFFER 20

DATABASE=PPRODS T1

MODE=SPLIT_AND_ LOAD

NOTNFS_DIR=/notnfs

CHECK_LEVEL=NOC HECK

TRACE=1
--------- End of autoloader.cfg ---------

[Ian]

CompGeek wrote:

Hi there,

I am trying to load a file with 1729 records into a DB2 UDB EEE 7.1
DataBase (on AIX 4.3) which is across two partitions. The partitioning
key is BI_INSTITUTIONA L_KEY which is a BIGINT and is explicitly
defined in the table DDL (with "using hashing" ON). The autoloader
config file is attached below.

The results of the autoloader are as :

--------- START OF AUTOLOAD OUTPUT ---------

Utility program: "db2atld". Version: "07026".
Start reading autoloader configuration file: autoloader.cfg.
Finish reading autoloader configuration file: autoloader.cfg.
Start initializing autoloader process.
Finish initializing autoloader process.
The AutoLoader is now issuing all LOAD requests.
The LOAD operation has begun on partition "1".
The LOAD operation has begun on partition "0".
SQL3011C There is not enough storage to process the command.
The AutoLoader is now issuing all split requests.
Start db2split on node "1" in background.
The AutoLoader is waiting for all splitters to complete.
The utility has completed reading "1" megabytes from the user data.
The AutoLoader is waiting for all LOAD operations to complete.
The remote execution of the splitter utility on partition "1"
finished with remote execution code "0".

Operation Node SQL Code Result
_______________ _______________ _______________ _______________ _______________ ___
LOAD 000 +00003107 Success.
_______________ _______________ _______________ _______________ _______________ ___
LOAD 001 -00003011 Nothing loaded. Table normal.
_______________ _______________ _______________ _______________ _______________ ___
SPLIT 001 +00000000 Success.
_______________ _______________ _______________ _______________ _______________ ___
PRE-SPLIT 000 +00000000 Success.
_______________ _______________ _______________ _______________ _______________ ___
RESULTS: 1 of 2 LOADs completed successfully.
_______________ _______________ _______________ _______________ _______________ ___
Rows Read 885
Rows Skipped 0
Rows Loaded 885
Rows Rejected 0
Rows Deleted 0
Rows Committed 885

--------- END OF AUTOLOAD OUTPUT ---------
As shown, only one LOAD succeeds by loading 885 rows.

The current settings of the HEAP parameters for the DB are as :

Database heap (4KB) (DBHEAP) = 20000
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 20000
Max appl. control heap size (4KB) (APP_CTL_HEAP_S Z) = 5000
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 32768
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Could someone please help me with the correct settings.

Any advice/pointers to web forum URLs will be most welcome.

Thanx.

NP.


--------- Start of autoloader.cfg ---------
############### ############### ############### ########
# Release level of this configuration file.
# Please do not delete or modify this line.
RELEASE=V5.01

db2 LOAD FROM ff_bad_institut ional.out OF DEL MODIFIED BY KEEPBLANKS
COLDEL| SAVECOUNT 20 MESSAGES BadInstLoad.msg INSERT INTO
HCSRODS.TB_BAD_ INSTITUTIONAL(B I_INSTITUTIONAL _KEY,BI_HEADER_ KEY,BI_ERROR_CO UNT,BI_RECORD_T YPE,BI_FILING_D ATE,BI_FILING_S TATE_OR_CNTRY_C ODE,BI_SEQUENCE _NUMBER,BI_TIME ,BI_HCSR_SUFFIX ,BI_PROGRAM_IND ICATOR,BI_HCSR_ PTC_DATE,BI_DAT E_ADJUSTMENT_ID ENTIFIED,BI_SPO NSOR_SSN,BI_SPO NSOR_PAY_GRADE, BI_SPONSOR_BRAN CH_OF_SERVICE,B I_SPONSOR_STATU S,BI_PATIENT_RE LATION_TO_SPONS OR,BI_PATIENT_N AME,BI_PATIENT_ SSN,BI_PAT
ENT_DOB,BI_DEER S_DEPENDENT_SUF FIX,BI_PATIENT_ SEX,BI_PATIENT_ ZIP_CODE,BI_ENR OLLMENT_STATUS, BI_NAS_NUMBER,B I_REASON_FOR_PM NT_REDUCTION,BI _AMOUNT_BILLED, BI_AMOUNT_ALLOW ED,BI_AMOUNT_PA ID_BY_OHI,BI_AM OUNT_ALLOWED_BY _OHI,BI_AMT_OF_ THIRDPARTY_LIAB ILITY,BI_AMOUNT _OF_PAYMENT_RED UCTION,BI_PATIE NT_COINSURANCE, BI_PATIENT_COPA YMENT,BI_AMT_PA ID_BY_GOVT_CONT RACTOR,BI_OVERR IDE_CODE,BI_TYP E_OF_SUBMISSION ,BI_NAS_EX
EPTION_REASON,B I_HEALTH_CARE_P LAN_CODE,BI_DIA G_EDITION_IDENT IFIER,BI_REASON _FOR_ADJUSTMENT ,BI_SPECIAL_PRO CESSING_CODE,BI _SPECIAL_RATE_C ODE,BI_MAJOR_DI AGNOSTIC_CATEGO RY,BI_REASON_FO R_ISSUANCE,BI_C LAIM_FORM_TYPE, BI_PCM_LOCATION _DMIS_ID_CODE,B I_NBR_OF_PMNT_R EDCN_DAYS_SVCS, BI_PROV_CONTRAC T_AFFLN_CODE,BI _PROV_STATE_OR_ CNTRY_CODE,BI_P ROVIDER_TAXPAYE R_NUMBER,BI_PRO VIDER_SUB_IDENT IFIER,BI_PROVID ER_ZIP_COD
,BI_PROVIDER_PA RTICIPATION_IND ,BI_TYPE_OF_INS TITUTION,BI_ADM ISSION_DATE,BI_ BILL_CLASSIFICA TION_CODE,BI_FR EQUENCY_CODE,BI _TYPE_OF_ADMISS ION,BI_SOURCE_O F_ADMISSION,BI_ DISCHARGE_STATU S,BI_BEGIN_DATE _OF_CARE,BI_END _DATE_OF_CARE,B I_NUMBER_OF_BIR THS,BI_TOTAL_BE D_DAYS,BI_GOVT_ AUTHORIZED_BED_ DAYS,BI_ADMISSI ON_DIAGNOSIS,BI _PRINCIPAL_TREA TMENT_DIAG,BI_S ECONDARY_TREATM ENT_DIAG_1,BI_S ECONDARY_TREATM ENT_DIAG_2
BI_SECONDARY_TR EATMENT_DIAG_3, BI_SECONDARY_TR EATMENT_DIAG_4, BI_SECONDARY_TR EATMENT_DIAG_5, BI_SECONDARY_TR EATMENT_DIAG_6, BI_SECONDARY_TR EATMENT_DIAG_7, BI_SECONDARY_TR EATMENT_DIAG_8, BI_PRINCIPAL_OP NSP_CODE,BI_SEC ONDARY_OPNSP_CO DE_1,BI_SECONDA RY_OPNSP_CODE_2 ,BI_SECONDARY_O PNSP_CODE_3,BI_ SECONDARY_OPNSP _CODE_4,BI_SECO NDARY_OPNSP_COD E_5,BI_DRG_NUMB ER,BI_DRG_GROUP ER_EDITION,BI_D RG_PRICER_EDITI ON,BI_REV_
ATA_OCCURRENCE_ COUNT,BI_D_TYPE _OF_SBMSN_SORT_ NUMBER,BI_D_REC ORD_RECVD_SEQ_N UMBER,BI_D_PATI ENT_PAY_TOTAL,B I_D_MTF_CODE,BI _D_PATIENT_AGE, BI_D_CYCLE_NUMB ER,BI_D_BENEFIT _CLAIM_COUNT_CO DE,BI_D_HOSP_DE PARTMENT_NUMBER ,BI_D_CARE_END_ FISCAL_YEAR,BI_ D_HEALTH_SVCS_R EGION_CODE,BI_D _BENEFICIARY_CA TEGORY,BI_D_ADM ISSION_COUNT_CO DE,BI_D_CATEGOR Y_OF_CARE,BI_D_ DRG_DERIVED_COD E,BI_ERROR_STRI NG,BI_S_AMOUNT_ BILLED,BI_
_AMOUNT_ALLOWED ,BI_S_AMOUNT_PA ID_BY_OHI,BI_S_ AMOUNT_ALLOWED_ BY_OHI,BI_S_AMT _OF_THIRDPARTY_ LIAB,BI_S_AMT_O F_PAYMENT_REDUC TION,BI_S_PATIE NT_COINSURANCE, BI_S_PATIENT_CO PAYMENT,BI_S_AM T_PAID_BY_GOVT_ CONTCTR,BI_S_NB R_PMNT_REDCN_DA YS_SVCS,BI_S_NU MBER_OF_BIRTHS, BI_S_TOTAL_BED_ DAYS,BI_S_GOVT_ AUTHORIZED_BED_ DAYS)
DATA BUFFER 20

DATABASE=PPRODS T1

MODE=SPLIT_AND_ LOAD

NOTNFS_DIR=/notnfs

CHECK_LEVEL=NOC HECK

TRACE=1
--------- End of autoloader.cfg ---------

Does your DB cfg on partition 1 matches what you posted (specifically
UTIL_HEAP_SZ)?

Also, using 'DATA BUFFER 20' seems pretty small to me. Have you tried
removing this option from the load command? (DB2 is pretty good about
choosing a default value).

Are you running multiple concurrent loads (i.e. is something else consuming
the utility heap when this load runs)?

Finally, loading very small files like this into a EEE database may be
faster with IMPORT -- the overhead of using the autoloader may outweigh
the performance increase.
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[CompGeek]

Ian <ia*****@mobile audio.com> wrote in message news:<40******* ***@corp.newsgr oups.com>...

CompGeek wrote:

Hi there,

I am trying to load a file with 1729 records into a DB2 UDB EEE 7.1
DataBase (on AIX 4.3) which is across two partitions. The partitioning
<SNIP>

--------- End of autoloader.cfg ---------

Does your DB cfg on partition 1 matches what you posted (specifically
UTIL_HEAP_SZ)?

How does one check for the UTIL_HEAP_SZ on partition 1 ?? Currently i
am issuing the command :

db2 get db cfg for <DBNAME>

to get the list of all the setting for that partitioned DB.
Also, using 'DATA BUFFER 20' seems pretty small to me. Have you tried
removing this option from the load command? (DB2 is pretty good about
choosing a default value).
I tried using this value of 20 because the DB2 help on SQL3011C
suggested using lesser buffers in the LOAD command. Even without this
option, the Autoloader fails.

Are you running multiple concurrent loads (i.e. is something else consuming
the utility heap when this load runs)?

No, this is the only process running on the DB2 AIX Server. Is there a
way to find out the list of applications using the UTIL_HEAP_SZ (Other
than the "db2 list applications" command ?? )

Finally, loading very small files like this into a EEE database may be
faster with IMPORT -- the overhead of using the autoloader may outweigh
the performance increase.

As specified, this DB is across 2 partitions. Can we use IMPORT for
the same ?? Do we have to import the file before or after splitting ??
Anywayz, I shall try it either way and let u know the results.

Good luck,

Thanx a billion,

Neil.

[Ian]

CompGeek wrote:

Ian <ia*****@mobile audio.com> wrote in message news:<40******* ***@corp.newsgr oups.com>...

CompGeek wrote:

Hi there,

I am trying to load a file with 1729 records into a DB2 UDB EEE 7.1
DataBase (on AIX 4.3) which is across two partitions. The partitioning

<SNIP>
--------- End of autoloader.cfg ---------

Does your DB cfg on partition 1 matches what you posted (specifically
UTIL_HEAP_SZ) ?

How does one check for the UTIL_HEAP_SZ on partition 1 ?? Currently i
am issuing the command :

db2 get db cfg for <DBNAME>

to get the list of all the setting for that partitioned DB.

The database configuration is unique on each partition. The GET DB CFG
command shows the database configuration for the current partition. You
would need to give the command, "db2_all db2 get db cfg for <db>" to get
the DB config for all partitions in your instance.

Finally, loading very small files like this into a EEE database may be
faster with IMPORT -- the overhead of using the autoloader may outweigh
the performance increase.

As specified, this DB is across 2 partitions. Can we use IMPORT for
the same ?? Do we have to import the file before or after splitting ??
Anywayz, I shall try it either way and let u know the results.

IMPORT does not require a split -- since it does normal inserts, DB2
takes care of the partitioning as it would for any normal application.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[CompGeek]

Thanx a Gadzillion, Ian.

After using db2_all to update the db cfg for the Node 1 ... the
autoloader worked fine.

Appreciate the help given.

Thanx.

Neil
Ian <ia*****@mobile audio.com> wrote in message news:<40******* ***@corp.newsgr oups.com>...

CompGeek wrote:

Ian <ia*****@mobile audio.com> wrote in message news:<40******* ***@corp.newsgr oups.com>...

CompGeek wrote:
Hi there,

I am trying to load a file with 1729 records into a DB2 UDB EEE 7.1
DataBase (on AIX 4.3) which is across two partitions. The partitioning

<SNIP>

--------- End of autoloader.cfg ---------
Does your DB cfg on partition 1 matches what you posted (specifically
UTIL_HEAP_SZ) ?

How does one check for the UTIL_HEAP_SZ on partition 1 ?? Currently i
am issuing the command :

db2 get db cfg for <DBNAME>

to get the list of all the setting for that partitioned DB.

The database configuration is unique on each partition. The GET DB CFG
command shows the database configuration for the current partition. You
would need to give the command, "db2_all db2 get db cfg for <db>" to get
the DB config for all partitions in your instance.

Finally, loading very small files like this into a EEE database may be
faster with IMPORT -- the overhead of using the autoloader may outweigh
the performance increase.

As specified, this DB is across 2 partitions. Can we use IMPORT for
the same ?? Do we have to import the file before or after splitting ??
Anywayz, I shall try it either way and let u know the results.

IMPORT does not require a split -- since it does normal inserts, DB2
takes care of the partitioning as it would for any normal application.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----