Issue with Bteq Export and Multiload

Tools

Issue with Bteq Export and Multiload

Hi,

I have a BTEQ export which was running as:

[highlight=#ffff11]*****************************************************[/highlight]
[font=Courier New]
.SET ERRORLEVEL 3807 SEVERITY 0;
DATABASE NEWSDATA_PPT;

.EXPORT DATA file = /ifiles/terappt/wip/hk_news_run_control.dat;

.IF ERRORCODE <> 0 THEN .QUIT 0010;

SELECT HOUSEKEEPING_DATE
FROM LOAD_HISTORY
WHERE TABLE_NAME = 'news_run_control';

.IF ACTIVITYCOUNT < 1 THEN QUIT 0020;

.IF ERRORCODE <> 0 THEN .QUIT 0030;

.EXPORT RESET;

.QUIT; [/font]

[highlight=#ffff11]*****************************************************[/highlight]

the above used to run fine ... but it gives "garbage" data (when I view it in either vi editor or cat the export file)

so (for sake of readability) I changed the code to:

[highlight=#ffff11]*****************************************************[/highlight]
[font=Courier New]
.SET ERRORLEVEL 3807 SEVERITY 0;
DATABASE NEWSDATA_PPT;

.EXPORT DATA file = /ifiles/terappt/wip/hk_news_run_control.dat;

SELECT HOUSEKEEPING_DATE
FROM LOAD_HISTORY
WHERE TABLE_NAME = 'news_run_control';

.IF ACTIVITYCOUNT < 1 THEN QUIT 0020;

.IF ERRORCODE <> 0 THEN .QUIT 0030;

.EXPORT RESET;

.QUIT; [/font]
[highlight=#ffff11]*****************************************************[/highlight]

The above is giving me the output (When I cat or vi the file ... I get)

[highlight=#ffff11]20090312[/highlight]

As soon as this export process completes, I run a multiload which deletes data from a certain table depending on the date value present in the /ifiles/terappt/wip/hk_news_run_control.dat file.

The issue is that the multi-load runs fine for the "junk" data date .. but when I run it against the date produced from the REPORT code ... I get the error:

[highlight=#ffff11]*****************************************************[/highlight]

[font=Courier New]
**** 09:11:40 UTY2416 Config file parameters in effect are: MAXSESS=24.
========================================================================
= =
= MultiLoad Utility Release MLOD.07.09.00.004 =
= Platform IBM-AIX =
= =
========================================================================
= =
= Copyright 1990-2008, NCR Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 09:11:40 UTY2411 Processing start date: SUN APR 12, 2009
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .logon dbc/pptuser,;
**** 09:11:40 UTY6214 Reminder: A .Logtable statement must be entered for a successful logon.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0002 /*****************************************************************/
/* Teradata generic multiload delete script for housekeeping */
/* --------------------------------------------------------- */
/* filepath path for housekeeping date parameter */
/* db1 database name (global) */
/* tmpdb1 name of temporary database (global) */
/* tag1 prefix for error, log and work tables */
/*****************************************************************/
.LOGTABLE TEMPDB_PPT.hk_news_run_control_log;
**** 09:11:40 UTY8400 Teradata Database Release: V2R.06.02.02.56
**** 09:11:40 UTY8400 Teradata Database Version: 06.02.02.56
**** 09:11:40 UTY8400 Default character set: ASCII
**** 09:11:40 UTY8400 Current RDBMS has interval support
**** 09:11:40 UTY8400 Current RDBMS has UDT support
**** 09:11:40 UTY8400 Maximum supported buffer size: 1M
**** 09:11:40 UTY8400 Data Encryption supported by RDBMS server
**** 09:11:40 UTY6211 A successful connect was made to the RDBMS.
**** 09:11:40 UTY6210 Logtable 'TEMPDB_PPT.hk_news_run_control_log' indicates that a restart
is in progress.

0003 .BEGIN DELETE MLOAD AMPCHECK NONE
TABLES NEWSDATA_PPT.news_run_control
WORKTABLES TEMPDB_PPT.hk_news_run_control_wrk
ERRORTABLES TEMPDB_PPT.hk_news_run_control_err1;
========================================================================
= =
= Processing MultiLoad Statements =
= =
========================================================================

0004 .LAYOUT HOUSEKEEPING_FILE;
0005 .FIELD HOUSEKEEPING_DATE 1 DATE;

0006 DELETE FROM NEWSDATA_PPT.news_run_control
WHERE processing_dt < :HOUSEKEEPING_DATE;

0007 .IMPORT infile /ifiles/terappt/wip/hk_news_run_control.dat

LAYOUT HOUSEKEEPING_FILE;

0008 .END MLOAD;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 09:11:40 UTY0829 Options in effect for this MultiLoad delete task:
. Tenacity: 4 hour limit to successfully connect load sessions.
. AmpCheck: Not in effect.
**** 09:11:40 UTY0817 MultiLoad submitting the following request:
Select NULL from TEMPDB_PPT.hk_news_run_control_log where (LogType = 125)
and (Seq = 1) and (MloadSeq = 0);
**** 09:11:40 UTY0817 MultiLoad submitting the following request:
Select NULL from TEMPDB_PPT.hk_news_run_control_log where (LogType = 120)
and (Seq = 1);
**** 09:11:40 UTY0812 MLOAD session(s) requested: 1.
**** 09:11:40 UTY0815 MLOAD session(s) connected: 1.
**** 09:11:40 UTY0817 MultiLoad submitting the following request:
BEGIN DELETE MLOAD NEWSDATA_PPT.news_run_control with
TEMPDB_PPT.hk_news_run_control_wrk errortables
TEMPDB_PPT.hk_news_run_control_err1;
**** 09:11:40 UTY0817 MultiLoad submitting the following request:
INS TEMPDB_PPT.hk_news_run_control_log (LogType, Seq, MLoadSeq)VALUES(130,
1, 40);
**** 09:11:40 UTY4015 Access module error '16' received during 'read' operation on record
number '0': 'Unexpected data format'
**** 09:11:40 UTY4015 Access module error '16' received during 'read' operation on record
number '0': 'Unexpected data format'
**** 09:11:40 UTY1819 Warning: More than one record found in Delete task IMPORT file. Only the
first record will be used.
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 09:11:40 UTY6212 A successful disconnect was made from the RDBMS.
**** 09:11:40 UTY2410 Total processor time used = '0.11 Seconds'
. Start : 09:11:40 - SUN APR 12, 2009
. End : 09:11:40 - SUN APR 12, 2009
. Highest return code encountered = '12'.[/font]

please advise as to where I could be going worng?

[highlight=#ffff11]*****************************************************[/highlight]

1 REPLY

Re: Issue with Bteq Export and Multiload



>>> I have modified the code to include "FORMAT text"

UXLPRMS02 terapppt:/u01/app/teradata/apppt/wip > cat TER_NW_HK_NEWS_RUN_CONTROL_hk_mload_delete.wip
.logon dbc/pptuser,pptuser;
/*****************************************************************/
/* Teradata generic multiload delete script for housekeeping */
/* --------------------------------------------------------- */
/* filepath path for housekeeping date parameter */
/* db1 database name (global) */
/* tmpdb1 name of temporary database (global) */
/* tag1 prefix for error, log and work tables */
/*****************************************************************/
.LOGTABLE TEMPDB_PPT.hk_news_run_control_log;

.BEGIN DELETE MLOAD AMPCHECK NONE
TABLES NEWSDATA_PPT.news_run_control
WORKTABLES TEMPDB_PPT.hk_news_run_control_wrk
ERRORTABLES TEMPDB_PPT.hk_news_run_control_err1;

.LAYOUT HOUSEKEEPING_FILE;
.FIELD HOUSEKEEPING_DATE 1 DATE;

DELETE FROM NEWSDATA_PPT.news_run_control
WHERE processing_dt < :HOUSEKEEPING_DATE;

.IMPORT infile /ifiles/terappt/wip/hk_news_run_control.dat
LAYOUT HOUSEKEEPING_FILE
FORMAT TEXT
;

.END MLOAD;
.LOGOFF;
UXLPRMS02 terapppt:/u01/app/teradata/apppt/wip >

>>> and the contents of the /ifiles/terappt/wip/hk_news_run_control.dat are:

UXLPRMS02 terapppt:/ifiles/terappt/wip > cat hk_news_run_control.dat
12/02/09
UXLPRMS02 terapppt:/ifiles/terappt/wip >

but after running the MLOAD, I am getting the following error now:

**** 13:51:05 UTY0817 MultiLoad submitting the following request:
BT;
**** 13:51:05 UTY0817 MultiLoad submitting the following request:
USING HOUSEKEEPING_DATE(DATE) DELETE FROM NEWSDATA_PPT.news_run_control
WHERE processing_dt < :HOUSEKEEPING_DATE;
**** 13:51:05 UTY0805 RDBMS failure, 2665: Invalid date.
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 13:51:05 UTY6215 The restart log table has NOT been dropped.
**** 13:51:05 UTY6212 A successful disconnect was made from the RDBMS.
**** 13:51:05 UTY2410 Total processor time used = '0.14 Seconds'
. Start : 13:51:04 - MON APR 13, 2009
. End : 13:51:05 - MON APR 13, 2009
. Highest return code encountered = '12'.