APT_TERA_64K_BUFFERSIZE

[bcarlson]

I am posting this as an informational posting, not as a question. We ran into this problem recently and since I didn't find information on DSXChange we pinged Teradata and IBM directly. Now that it is resolved, I am posting the problem and resolution. I'll use multiple posts to make it easier to follow the progression (and it boosts my posting count!)

Initial problem statement:

I have a question about a DBC error that we are getting with an ADW job in DataStage 7.5.1a (old school!).

We are getting a DBC error code 305:

adwdm_place_homemortgagecrossreference_work: TeraGenericQuery Error:
Request Close failed: Error Code = 305 Session return code = 305 , DBC
return code = 305 [genericquery.C:841] DBC message: CLI2:
NOREQUEST(305): Specified request does not exist. [genericquery.C:842]

I found a reference to it in the Messages PDF under the Network-Attached CLIv2 Messages, but it simply repeats the same error message listed about with a remedy of "Check the requested id". I don't suppose you could shed some light on this, could you?

[bcarlson]

Upon further review, here is a more complete picture of what is going on:

Node 0 has a rejected buffer write warning, followed by a Error Code = 350. Nodes 0-7 each process 100,000 records and then fail. The Error Code 350 is repeated, then a return code of 311. The final error message is an error code 305.

The following are the Messages from the TD docs (Messages / Chap 19 Network-Attached CLIv2 Messages):

350 Request size exceeds maximum.
Explanation: The length of the request exceeds the
maximum supported length.
Notes: None
Remedy: Reduce the length of the request.

311 NOACTIVE No active sessions.
Explanation: DBCHWAT could not find any active
sessions.
Remedy: No action required.

305 NOREQUEST Specified request does not
exist.
Explanation: The request could not be found.
Remedy: Check the request id.

The data going into the target table has the following schema:

Code: Select all

record
( SERVICINGUID: int32;
  UNIQUEADDRESSID_ORIG: nullable int32;
  UNIQUEADDRESSID_SERVICING: nullable int32;
  LOAN_NUM_EXPANDED: nullable string[max=10];
  ALLTEL_CLIENT: nullable string[max=3];
  LOAN_ID: nullable string[max=12];
  ORIGADDRESSVALIDATEDFLAG: nullable string[max=1];
  SERVICINGADDRESSVALIDATEDFLAG: nullable string[max=1];
  UNIQUEADDRESSID: nullable int32;
  LOANNUM: nullable string[max=10];
)

The table definition is the following:

Code: Select all

CREATE SET TABLE adwdm_place.homemortgagecrossreference ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      SERVICINGUID INTEGER NOT NULL,
      UNIQUEADDRESSID_ORIG INTEGER,
      UNIQUEADDRESSID_SERVICING INTEGER,
      LOAN_NUM_EXPANDED VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ALLTEL_CLIENT VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      LOAN_ID VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORIGADDRESSVALIDATEDFLAG VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      SERVICINGADDRESSVALIDATEDFLAG VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      UNIQUEADDRESSID INTEGER,
      LOANNUM VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      LOAD_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6))
UNIQUE PRIMARY INDEX UPI_HOMEMORTGAGECROSSREFERENCE ( SERVICINGUID );

The logs for the job show a successful run with a volume under 50K. It also shows the failed execution with a high volume around 8.5M.

Brad.

[bcarlson]

We update the dsenv profile and bounced DataStage. The jobs ran successfully. We'll be migrating this same change to the rest of our environments after another week or so to prove that no other Teradata jobs are negatively impacted. Tom did not expect any negative repercussions of the change.

Hope these postings help.

Brad.

[ray.wurlod]

Verr-rr-ry interesting! Will put this in favorites for next time I'm working at a Teradata site.

Isn't it too cold to type where you are?

[bcarlson]

It is only -8 F here. A few days ago it was -13. That's what hot coffee is for.

[sandeepgs]

Hi can some one tell us how to add the environment variables defined to the dsenv file

[ray.wurlod]

dsenv is a text file. Look at the examples that are already in there. You need to set a value for the environment variable and you also need to export that environment variable (because dsenv is executed using a Bourne shell).

Note also that there is no dsenv script if your DataStage server is on a Windows platform. In this case you configure environment variables using Control Panel (and, probably, re-boot).

[SBSA_DW3]

Hi,

In version 8.1 I see "APT_TERA_64K_BUFFERS" variable is of boolean data type and couldn't find "APT_TERA_64K_BUFFERSIZE" variable (using administrator environmental variables page).

We are having similar problem and I would like to set this variable as suggested.

Regards,
Amit

[chulett]

Not sure what's stopping you. Simply create the second one noted as User Defined if it's not in the official list of environment variables.