SEO

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_INSTITUTIONAL_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_SZ) = 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_institutional.out OF DEL MODIFIED BY KEEPBLANKS
COLDEL| SAVECOUNT 20 MESSAGES BadInstLoad.msg INSERT INTO
HCSRODS.TB_BAD_INSTITUTIONAL(BI_INSTITUTIONAL_KEY, BI_HEADER_KEY,BI_ERROR_COUNT,BI_RECORD_TYPE,BI_FIL ING_DATE,BI_FILING_STATE_OR_CNTRY_CODE,BI_SEQUENCE _NUMBER,BI_TIME,BI_HCSR_SUFFIX,BI_PROGRAM_INDICATO R,BI_HCSR_PTC_DATE,BI_DATE_ADJUSTMENT_IDENTIFIED,B I_SPONSOR_SSN,BI_SPONSOR_PAY_GRADE,BI_SPONSOR_BRAN CH_OF_SERVICE,BI_SPONSOR_STATUS,BI_PATIENT_RELATIO N_TO_SPONSOR,BI_PATIENT_NAME,BI_PATIENT_SSN,BI_PAT
ENT_DOB,BI_DEERS_DEPENDENT_SUFFIX,BI_PATIENT_SEX,B I_PATIENT_ZIP_CODE,BI_ENROLLMENT_STATUS,BI_NAS_NUM BER,BI_REASON_FOR_PMNT_REDUCTION,BI_AMOUNT_BILLED, BI_AMOUNT_ALLOWED,BI_AMOUNT_PAID_BY_OHI,BI_AMOUNT_ ALLOWED_BY_OHI,BI_AMT_OF_THIRDPARTY_LIABILITY,BI_A MOUNT_OF_PAYMENT_REDUCTION,BI_PATIENT_COINSURANCE, BI_PATIENT_COPAYMENT,BI_AMT_PAID_BY_GOVT_CONTRACTO R,BI_OVERRIDE_CODE,BI_TYPE_OF_SUBMISSION,BI_NAS_EX
EPTION_REASON,BI_HEALTH_CARE_PLAN_CODE,BI_DIAG_EDI TION_IDENTIFIER,BI_REASON_FOR_ADJUSTMENT,BI_SPECIA L_PROCESSING_CODE,BI_SPECIAL_RATE_CODE,BI_MAJOR_DI AGNOSTIC_CATEGORY,BI_REASON_FOR_ISSUANCE,BI_CLAIM_ FORM_TYPE,BI_PCM_LOCATION_DMIS_ID_CODE,BI_NBR_OF_P MNT_REDCN_DAYS_SVCS,BI_PROV_CONTRACT_AFFLN_CODE,BI _PROV_STATE_OR_CNTRY_CODE,BI_PROVIDER_TAXPAYER_NUM BER,BI_PROVIDER_SUB_IDENTIFIER,BI_PROVIDER_ZIP_COD
,BI_PROVIDER_PARTICIPATION_IND,BI_TYPE_OF_INSTITUT ION,BI_ADMISSION_DATE,BI_BILL_CLASSIFICATION_CODE, BI_FREQUENCY_CODE,BI_TYPE_OF_ADMISSION,BI_SOURCE_O F_ADMISSION,BI_DISCHARGE_STATUS,BI_BEGIN_DATE_OF_C ARE,BI_END_DATE_OF_CARE,BI_NUMBER_OF_BIRTHS,BI_TOT AL_BED_DAYS,BI_GOVT_AUTHORIZED_BED_DAYS,BI_ADMISSI ON_DIAGNOSIS,BI_PRINCIPAL_TREATMENT_DIAG,BI_SECOND ARY_TREATMENT_DIAG_1,BI_SECONDARY_TREATMENT_DIAG_2
BI_SECONDARY_TREATMENT_DIAG_3,BI_SECONDARY_TREATME NT_DIAG_4,BI_SECONDARY_TREATMENT_DIAG_5,BI_SECONDA RY_TREATMENT_DIAG_6,BI_SECONDARY_TREATMENT_DIAG_7, BI_SECONDARY_TREATMENT_DIAG_8,BI_PRINCIPAL_OPNSP_C ODE,BI_SECONDARY_OPNSP_CODE_1,BI_SECONDARY_OPNSP_C ODE_2,BI_SECONDARY_OPNSP_CODE_3,BI_SECONDARY_OPNSP _CODE_4,BI_SECONDARY_OPNSP_CODE_5,BI_DRG_NUMBER,BI _DRG_GROUPER_EDITION,BI_DRG_PRICER_EDITION,BI_REV_
ATA_OCCURRENCE_COUNT,BI_D_TYPE_OF_SBMSN_SORT_NUMBE R,BI_D_RECORD_RECVD_SEQ_NUMBER,BI_D_PATIENT_PAY_TO TAL,BI_D_MTF_CODE,BI_D_PATIENT_AGE,BI_D_CYCLE_NUMB ER,BI_D_BENEFIT_CLAIM_COUNT_CODE,BI_D_HOSP_DEPARTM ENT_NUMBER,BI_D_CARE_END_FISCAL_YEAR,BI_D_HEALTH_S VCS_REGION_CODE,BI_D_BENEFICIARY_CATEGORY,BI_D_ADM ISSION_COUNT_CODE,BI_D_CATEGORY_OF_CARE,BI_D_DRG_D ERIVED_CODE,BI_ERROR_STRING,BI_S_AMOUNT_BILLED,BI_
_AMOUNT_ALLOWED,BI_S_AMOUNT_PAID_BY_OHI,BI_S_AMOUN T_ALLOWED_BY_OHI,BI_S_AMT_OF_THIRDPARTY_LIAB,BI_S_ AMT_OF_PAYMENT_REDUCTION,BI_S_PATIENT_COINSURANCE, BI_S_PATIENT_COPAYMENT,BI_S_AMT_PAID_BY_GOVT_CONTC TR,BI_S_NBR_PMNT_REDCN_DAYS_SVCS,BI_S_NUMBER_OF_BI RTHS,BI_S_TOTAL_BED_DAYS,BI_S_GOVT_AUTHORIZED_BED_ DAYS)
DATA BUFFER 20

DATABASE=PPRODST1

MODE=SPLIT_AND_LOAD

NOTNFS_DIR=/notnfs

CHECK_LEVEL=NOCHECK

TRACE=1


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

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_INSTITUTIONAL_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_SZ) = 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_institutional.out OF DEL MODIFIED BY KEEPBLANKS
> COLDEL| SAVECOUNT 20 MESSAGES BadInstLoad.msg INSERT INTO
> HCSRODS.TB_BAD_INSTITUTIONAL(BI_INSTITUTIONAL_KEY, BI_HEADER_KEY,BI_ERROR_COUNT,BI_RECORD_TYPE,BI_FIL ING_DATE,BI_FILING_STATE_OR_CNTRY_CODE,BI_SEQUENCE _NUMBER,BI_TIME,BI_HCSR_SUFFIX,BI_PROGRAM_INDICATO R,BI_HCSR_PTC_DATE,BI_DATE_ADJUSTMENT_IDENTIFIED,B I_SPONSOR_SSN,BI_SPONSOR_PAY_GRADE,BI_SPONSOR_BRAN CH_OF_SERVICE,BI_SPONSOR_STATUS,BI_PATIENT_RELATIO N_TO_SPONSOR,BI_PATIENT_NAME,BI_PATIENT_SSN,BI_PAT
> ENT_DOB,BI_DEERS_DEPENDENT_SUFFIX,BI_PATIENT_SEX,B I_PATIENT_ZIP_CODE,BI_ENROLLMENT_STATUS,BI_NAS_NUM BER,BI_REASON_FOR_PMNT_REDUCTION,BI_AMOUNT_BILLED, BI_AMOUNT_ALLOWED,BI_AMOUNT_PAID_BY_OHI,BI_AMOUNT_ ALLOWED_BY_OHI,BI_AMT_OF_THIRDPARTY_LIABILITY,BI_A MOUNT_OF_PAYMENT_REDUCTION,BI_PATIENT_COINSURANCE, BI_PATIENT_COPAYMENT,BI_AMT_PAID_BY_GOVT_CONTRACTO R,BI_OVERRIDE_CODE,BI_TYPE_OF_SUBMISSION,BI_NAS_EX
> EPTION_REASON,BI_HEALTH_CARE_PLAN_CODE,BI_DIAG_EDI TION_IDENTIFIER,BI_REASON_FOR_ADJUSTMENT,BI_SPECIA L_PROCESSING_CODE,BI_SPECIAL_RATE_CODE,BI_MAJOR_DI AGNOSTIC_CATEGORY,BI_REASON_FOR_ISSUANCE,BI_CLAIM_ FORM_TYPE,BI_PCM_LOCATION_DMIS_ID_CODE,BI_NBR_OF_P MNT_REDCN_DAYS_SVCS,BI_PROV_CONTRACT_AFFLN_CODE,BI _PROV_STATE_OR_CNTRY_CODE,BI_PROVIDER_TAXPAYER_NUM BER,BI_PROVIDER_SUB_IDENTIFIER,BI_PROVIDER_ZIP_COD
> ,BI_PROVIDER_PARTICIPATION_IND,BI_TYPE_OF_INSTITUT ION,BI_ADMISSION_DATE,BI_BILL_CLASSIFICATION_CODE, BI_FREQUENCY_CODE,BI_TYPE_OF_ADMISSION,BI_SOURCE_O F_ADMISSION,BI_DISCHARGE_STATUS,BI_BEGIN_DATE_OF_C ARE,BI_END_DATE_OF_CARE,BI_NUMBER_OF_BIRTHS,BI_TOT AL_BED_DAYS,BI_GOVT_AUTHORIZED_BED_DAYS,BI_ADMISSI ON_DIAGNOSIS,BI_PRINCIPAL_TREATMENT_DIAG,BI_SECOND ARY_TREATMENT_DIAG_1,BI_SECONDARY_TREATMENT_DIAG_2
> BI_SECONDARY_TREATMENT_DIAG_3,BI_SECONDARY_TREATME NT_DIAG_4,BI_SECONDARY_TREATMENT_DIAG_5,BI_SECONDA RY_TREATMENT_DIAG_6,BI_SECONDARY_TREATMENT_DIAG_7, BI_SECONDARY_TREATMENT_DIAG_8,BI_PRINCIPAL_OPNSP_C ODE,BI_SECONDARY_OPNSP_CODE_1,BI_SECONDARY_OPNSP_C ODE_2,BI_SECONDARY_OPNSP_CODE_3,BI_SECONDARY_OPNSP _CODE_4,BI_SECONDARY_OPNSP_CODE_5,BI_DRG_NUMBER,BI _DRG_GROUPER_EDITION,BI_DRG_PRICER_EDITION,BI_REV_
> ATA_OCCURRENCE_COUNT,BI_D_TYPE_OF_SBMSN_SORT_NUMBE R,BI_D_RECORD_RECVD_SEQ_NUMBER,BI_D_PATIENT_PAY_TO TAL,BI_D_MTF_CODE,BI_D_PATIENT_AGE,BI_D_CYCLE_NUMB ER,BI_D_BENEFIT_CLAIM_COUNT_CODE,BI_D_HOSP_DEPARTM ENT_NUMBER,BI_D_CARE_END_FISCAL_YEAR,BI_D_HEALTH_S VCS_REGION_CODE,BI_D_BENEFICIARY_CATEGORY,BI_D_ADM ISSION_COUNT_CODE,BI_D_CATEGORY_OF_CARE,BI_D_DRG_D ERIVED_CODE,BI_ERROR_STRING,BI_S_AMOUNT_BILLED,BI_
> _AMOUNT_ALLOWED,BI_S_AMOUNT_PAID_BY_OHI,BI_S_AMOUN T_ALLOWED_BY_OHI,BI_S_AMT_OF_THIRDPARTY_LIAB,BI_S_ AMT_OF_PAYMENT_REDUCTION,BI_S_PATIENT_COINSURANCE, BI_S_PATIENT_COPAYMENT,BI_S_AMT_PAID_BY_GOVT_CONTC TR,BI_S_NBR_PMNT_REDCN_DAYS_SVCS,BI_S_NUMBER_OF_BI RTHS,BI_S_TOTAL_BED_DAYS,BI_S_GOVT_AUTHORIZED_BED_ DAYS)
> DATA BUFFER 20
>
> DATABASE=PPRODST1
>
> MODE=SPLIT_AND_LOAD
>
> NOTNFS_DIR=/notnfs
>
> CHECK_LEVEL=NOCHECK
>
> 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! =-----

Ian <ianbjor@mobileaudio.com> wrote in message news:<4006b5e3$1_1@corp.newsgroups.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.

CompGeek wrote:
> Ian <ianbjor@mobileaudio.com> wrote in message news:<4006b5e3$1_1@corp.newsgroups.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! =-----

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 <ianbjor@mobileaudio.com> wrote in message news:<40079213$1_1@corp.newsgroups.com>...
> CompGeek wrote:
> > Ian <ianbjor@mobileaudio.com> wrote in message news:<4006b5e3$1_1@corp.newsgroups.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! =-----