Need help getting rid of parcel length error in BTEQ script

Database
Enthusiast

Need help getting rid of parcel length error in BTEQ script

Hello everyone,

I’m having an issue with a BTEQ script. The objective of the script is to:

  1. Perform a SELECT on DBC.Databases for DatabaseName; this generates a list of all of our databases on that TPDID.
  2. Export that list of names to a file.
  3. Call the database names from that file as inputs to a macro called ShowDatabase.
  4. The macro is supposed to generate the DDL, list of roles/users with privileges on the database and their privs based upon being supplied the DatabaseName.
  5. Export all of this info for each database to a second file.

 Here is the BTEQ script:

.logon <node IP address>/<myusername>,<mypassword>;

.export report file=c:\bteq\dbnames.txt;

SELECT DatabaseName (TITLE '') FROM "DBC".Databases;

.export reset;

.export report file=c:\bteq\dbdefs.txt;

.import report file=c:\bteq\dbnames.txt;

Using (DatabaseName varchar(30))

EXECUTE DBADMIN.ShowDatabase (:DatabaseName);

.Repeat *;

.export reset;

.quit;

The script errors after the EXECUTE statement; the messaging is:

***Growing buffer to 65473

***Failure 2673 The source parcel length does not match data that was defined.

Now the DatabaseName field in DBC.Databases is derived from DBC.Dbase. In Dbase, the field is a varchar (128); the DBC.Databases view CASTS the field as char (30). The ShowDatabase macro sets the DatabaseName as a varchar (30), as shown from the first few lines of the macro below:

CREATE MACRO DBADMIN.ShowDatabase (

    DatabaseName VARCHAR(30)

)

AS

(

    SELECT

        'CREATE DATABASE '

        || TRIM(DatabaseName)

I have tried changing the USING statement in the BTEQ script to call DatabaseName as a char(30) and as a varchar (128). This yielded no different result. I also tried SELECTING directly from DBC.Dbase instead of DBC.Databases; this also resulted in no change to the error.

Any and all help on this would be greatly appreciated.  Thanks!

6 REPLIES
Enthusiast

Re: Need help getting rid of parcel length error in BTEQ script

Hi,

When you import a file using BTEQ Import, Bteq try to combine as many records in a parcel as it can fit. So in your case you parcel length is exceeding the length CLI can handle.

You can avoid this error by using .PACK, this will limit the number of records which are being combined in a BTEQ import request. 

for example you can place the .PACK 100, or replace 100 with a smaller value,  until your parcels becomes within limit of CLI.

Place this before .Import command.

Hope it will help,

Khurram
Senior Apprentice

Re: Need help getting rid of parcel length error in BTEQ script

The default PACK factor in BTEQ is always 1, so changing it will not fix the problem.

REPORT format expects only fixed length CHARs, when you define a VARCHAR the first two chars are treated as the binary length of that VARCHAR. 

When you change to a CHAR it fails because a BTEQ EXPORT never writes trailing blanks in REPORT mode.

There are two possible solutions:

Change your import to VARTEXT

.import VARTEXT file=c:\bteq\dbnames.txt;

Or use DATA mode for import and export the names as CHAR(30).

Enthusiast

Re: Need help getting rid of parcel length error in BTEQ script

Hi Diether,

That is a good addition to my knowledge, thanks for correcting me :)

Khurram
Enthusiast

Re: Need help getting rid of parcel length error in BTEQ script

Hello again,

Thank you for the feedback. Out of an abundance of curiosity, I tried both of Dieter ‘s suggestions. First, I tried changing the .IMPORT to VARTEXT as recommended; I must confess I was not aware of the VARTEXT option. This worked, but only returned the database definition and other queried data for the first name in the list from dbnames.txt. The script then ended, without repeating the process for the other 235 databases on the TPDID.

I then re-edited the BTEQ script to try using the DATA mode importing, and changing to CHAR(30) for export, as shown below:

.logon <TPDID>/<myusername>,<mypassword>;

.export DATA file=c:\bteq\dbnames.txt;

SELECT DatabaseName (TITLE '') FROM "DBC".Databases;

.export reset;

.export report file=c:\bteq\dbdefs.txt;

.import DATA file=c:\bteq\dbnames.txt;

Using (DatabaseName char(30))

EXECUTE DBADMIN.ShowDatabase (:DatabaseName);

.Repeat *;

.export reset;

.quit;

This also worked, but as with the VARTEXT edit, the script only returned the desired results for the first named database in the dbnames.txt file. The script then ended, without repeating the process as above.

At this point, it appears that the .REPEAT * statement is not looping the execution of the macro through all of the listed names in dbnames.txt.  Is the placement of the .REPEAT statement incorrect, ie should I move it before the EXECUTE? If so, that is counter to my understanding of how .REPEAT works. Or am I missing an additional statement that will allow the macro to execute for each name in the dbnames.txt list (as mentioned previously, there are over 230 db names in that list)? Thanks!

Mike

Senior Apprentice

Re: Need help getting rid of parcel length error in BTEQ script

Hi Mike,

yes, REPEAT repeats the following SQL-command. In your case it run the ";" 230 times :-)

.REPEAT *
EXECUTE DBADMIN.ShowDatabase (:DatabaseName);

There's "=n" which repeats the previous SQL command n times, but there's no "*".

Enthusiast

Re: Need help getting rid of parcel length error in BTEQ script

Hi Dieter,

Thank you for clarifying the usage of REPEAT. Moving the statement up to precede the EXECUTE did the trick. Here is a copy of the final BTEQ script, which ran flawlessly and created definitions for 236 databases:

.logon <TDPID>/<myusername>,<mypassword>;

.export DATA file=c:\bteq\dbnames.txt;

SELECT DatabaseName (TITLE '') FROM "DBC".Databases;

.export reset;

.export report file=c:\bteq\dbdefs.txt;

.import DATA file=c:\bteq\dbnames.txt;

.Repeat *;

Using (DatabaseName char(30))

EXECUTE DBADMIN.ShowDatabase (:DatabaseName);

.export reset;

.quit;

Thanks again for all of your help! :-)

Mike