CompGeek wrote:
[color=blue]
> 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 ---------[/color]
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! =-----
