DB2 EEE autoload fails due to insufficient storage (SQL3011C)

**Ian** · Nov 12 '05, 06:14 AM

Re: DB2 EEE autoload fails due to insufficient storage (SQL3011C)

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_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 ---------[/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! =-----

**CompGeek** · Nov 12 '05, 06:15 AM

Re: DB2 EEE autoload fails due to insufficient storage (SQL3011C)

Ian <ianbjor@mobile audio.com> wrote in message news:<4006b5e3$ 1_1@corp.newsgr oups.com>...[color=blue]
> CompGeek wrote:
>[color=green]
> > 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[/color][/color]

<SNIP>
[color=blue][color=green]
> >
> > --------- End of autoloader.cfg ---------[/color]
>
>
> Does your DB cfg on partition 1 matches what you posted (specifically
> UTIL_HEAP_SZ)?
>[/color]

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.
[color=blue]
> 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).[/color]

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.
[color=blue]
>
> Are you running multiple concurrent loads (i.e. is something else consuming
> the utility heap when this load runs)?
>[/color]

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 ?? )

[color=blue]
> 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.
>[/color]

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.

[color=blue]
> Good luck,
>[/color]

Thanx a billion,

Neil.

**Ian** · Nov 12 '05, 06:15 AM

Re: DB2 EEE autoload fails due to insufficient storage (SQL3011C)

CompGeek wrote:[color=blue]
> Ian <ianbjor@mobile audio.com> wrote in message news:<4006b5e3$ 1_1@corp.newsgr oups.com>...
>[color=green]
>>CompGeek wrote:
>>
>>[color=darkred]
>>>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[/color][/color]
>
>
> <SNIP>
>[color=green][color=darkred]
>>>--------- End of autoloader.cfg ---------[/color]
>>
>>
>>Does your DB cfg on partition 1 matches what you posted (specifically
>>UTIL_HEAP_SZ) ?
>>[/color]
>
>
> 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.[/color]

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.

[color=blue][color=green]
>>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.
>>[/color][/color]
[color=blue]
> 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.[/color]

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** · Nov 12 '05, 06:15 AM

Re: DB2 EEE autoload fails due to insufficient storage (SQL3011C)

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@mobile audio.com> wrote in message news:<40079213$ 1_1@corp.newsgr oups.com>...[color=blue]
> CompGeek wrote:[color=green]
> > Ian <ianbjor@mobile audio.com> wrote in message news:<4006b5e3$ 1_1@corp.newsgr oups.com>...
> >[color=darkred]
> >>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[/color]
> >
> >
> > <SNIP>
> >[color=darkred]
> >>>--------- End of autoloader.cfg ---------
> >>
> >>
> >>Does your DB cfg on partition 1 matches what you posted (specifically
> >>UTIL_HEAP_SZ) ?
> >>[/color]
> >
> >
> > 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.[/color]
>
> 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.
>
>[color=green][color=darkred]
> >>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.
> >>[/color][/color]
>[color=green]
> > 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.[/color]
>
> 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! =-----[/color]

DB2 EEE autoload fails due to insufficient storage (SQL3011C)

DB2 EEE autoload fails due to insufficient storage (SQL3011C)

Comment

Comment

Comment

Comment