load(DB2) doesn`t work

[DSguru2B]

DO an exact search on 'SQL27959N'.

[fmartinsferreira]

DO an exact search on 'SQL27959N'.

Brian a complete DB2 search give me the following:

--------------------------------------

SQL27959N The partitioned database configuration option
"<option-name>" is invalid. Reason code =
"<reason-code>".

Explanation:

The partitioned database configuration option named in the error
message is incorrectly specified or is incompatible with one of
the other load options specified.

The possible reason codes are as follows:


1 Partitioned database configuration options cannot be specified
in a non-partitioned database environment or when the
DB2_PARTITIONEDLOAD_DEFAULT registry variable is set to OFF.

2 Partitioned database configuration options may not be
specified more than once.

3 Invalid pointer detected in the piPartLoadInfoIn input
structure passed to the db2Load API.

4 Invalid pointer detected in the poPartLoadInfoOut output
structure passed to the db2Load API.

5 The argument supplied to the MODE option must be one of the
following:

o PARTITION_AND_LOAD
o PARTITION_ONLY
o LOAD_ONLY
o LOAD_ONLY_VERIFY_PART
o ANALYZE


6 The maximum number of partitioning agents must be less than or
equal to the maximum number of partitions allowable in a
cluster.

7 The maximum number of partition numbers in a partition list
must be less than or equal to the maximum number of partitions
allowable in a cluster.

8 The arguments supplied to the ISOLATE_PART_ERRS option must be
one of the following:

o SETUP_ERRS_ONLY
o LOAD_ERRS_ONLY
o SETUP_AND_LOAD_ERRS
o NO_ISOLATION


9 The value supplied to the STATUS_INTERVAL option must be in
the range 1-4000.

10 The maximum port number must be greater than or equal to the
minimum port number.

11 The only legal arguments for the CHECK_TRUNCATION, NEWLINE
and OMIT_HEADER options are TRUE and FALSE.

12 The argument supplied to RUN_STAT_DBPARTNUM must be a legal
partition number.

13 If the mode is ANALYZE, the MAP_FILE_OUTPUT option must be
specified.


14 If the mode is PARTITION_ONLY or LOAD_ONLY and a remote
client is being used, then the PART_FILE_LOCATION option must be
specified. If the mode is PARTITION_ONLY or LOAD_ONLY then if the
file type is CURSOR, the PART_FILE_LOCATION option must be used
and must specify a file name.

15 The load actions RESTART and TERMINATE can only be used when
the mode is PARTITION_AND_LOAD, LOAD_ONLY or
LOAD_ONLY_VERIFY_PART.

16 The HOSTNAME option cannot be specified unless the
FILE_TRANSFER_CMD option is also specified.

17 The partition isolation error modes LOAD_ERRS_ONLY and
SETUP_AND_LOAD_ERRS cannot be used when both the ALLOW READ
ACCESS or COPY YES options of the load command are used.

18 The LOAD_ONLY and LOAD_ONLY_VERIFY_PART modes are not
compatible with the CLIENT option of the load command.

User Response:

Resubmit the LOAD command with the correct partitioned database
configuration options.

--------------------------------------

I think that is a DB2 issue but the DBA think that is a DataStage issue.

How can I resubmit the LOAD command with the correct partitioned database configuration options?

Regards,

Fernando

[ray.wurlod]

Try disabling automatic load and inspecting the control file generated by DataStage.

[bcarlson]

SQL27959N The partitioned database configuration option
"<option-name>" is invalid. Reason code =
"<reason-code>".

Explanation:

The partitioned database configuration option named in the error
message is incorrectly specified or is incompatible with one of
the other load options specified.

The possible reason codes are as follows:


1 Partitioned database configuration options cannot be specified
in a non-partitioned database environment or when the
DB2_PARTITIONEDLOAD_DEFAULT registry variable is set to OFF.

This is the only reason you need to look at, since you original error message specifically mentions reason code 1.

Check to see if your database is partitioned - your DBA should be able to determine that.

What DB2 version are you using? I checked on the IBM website, and it looks like the variable DB2_PARTITIONEDLOAD_DEFAULT may not be necessary for v8.1 or later. We are using 8.2 and it is not set. You can determine its value with the db2set command:

db2set -i <instancename> <variablename>

ex.

Code: Select all

[myuserid@myserver] % db2set -i db2edwd1 DB2_PARTITIONEDLOAD_DEFAULT


DBI1303W Variable not set.

Explanation:

The variable was not set in the profile registry.

User Response:

No further action is required.

Hope this helps,

Brad

[fmartinsferreira]

Try disabling automatic load ...

Sorry Ray, but how can I do this?

Other thing: inspecting the control file generated by DataStage

The log show me the following:

1 - DB2 Load Binary Error.
2 - SQLCODE = -27959;
SQLSTATE= /datastage/scratch/ordb21974350690d9f3f_
3 - SQL27959N The partitioned database configuration
option "PARTITIONED DB CONFIG" is invalid.
Reason code = "1".
4 - Failed opening fifo /datastage/scratch/ordb21974350690d9f3f_.000
after 90 seconds: No such device or address.
5 - Errors during load (child returned 2); please examine the
file /datastage/scratch/APT_DB2_LOADMSG_epi030.
6 - The runLocally() of the operator failed.
7 - Operator terminated abnormally: runLocally did not return
APT_StatusOk
8 - main_program: Step execution finished with status = FAILED.

Well, about:
2 - The file "/datastage/scratch/ordb21974350690d9f3f_" doesn't exist.
3 - The database already have the DPF feature and
DB2_PARTITIONEDLOAD_DEFAULT registry variable is set to ON
4 - The file "/datastage/scratch/ordb21974350690d9f3f_.000" doesn't
exist.
5 - The file "/datastage/scratch/APT_DB2_LOADMSG_epi030" doesn't
exist.

Fernando

[fmartinsferreira]

What DB2 version are you using? I checked on the IBM website, and it looks like the variable DB2_PARTITIONEDLOAD_DEFAULT may not be necessary for v8.1 or later. We are using 8.2 and it is not set. You can determine its value with the db2set command:

Brad, second my DBA we are using v8.2 and the variable DB2_PARTITIONEDLOAD_DEFAULT is ON.

Thanks,

Fernando

[fmartinsferreira]

Check to see if your database is partitioned - your DBA should be able to determine that.
Brad

The manual says:

The database must be DB2 Enterprise Server Edition with the Data Partitioning Facility (DPF) option installed.

That was the problem, it is not enough only the DPF feature installed. To work we need to partition the database.

Thanks all, :D

Fernando

[ray.wurlod]

Boa sorte! :D