Fastload query error - .Execute the query one query at a time

Database
Enthusiast

Fastload query error - .Execute the query one query at a time

Hi Forum,

 

I'm trying to run a fastload query but keep getting this error.

"Execute the query one query at a time"

I've done the INSERT using a 'Create Table >> Insert Values' which works fine & I've made sure that 'File >> Import' is not selected.

I've looked around everywhere, but I thought I had this syntax down correctly??

Can anyone point out where I'm wrong?

Thanks

 

SESSIONS 4;
RECORD 1 THRU 100;
ERRLIMIT 25;
LOGON tdpid/User,password;
DROP TABLE AA_PB_TBL_AUS_STATES;
CREATE TABLE AA_PB_TBL_AUS_STATES, NO FALLBACK
(
STATE_ID VARCHAR(2),
STATE VARCHAR(30),
ABBR CHAR(2),
CAPITAL VARCHAR(30),
LARGEST_CITY VARCHAR(30)
)
PRIMARY INDEX (STATE_ID);

DEFINE 
STATE_ID (VARCHAR(2)),
STATE (VARCHAR(30)),
ABBR (VARCHAR(2)),
CAPITAL (VARCHAR(30)),
LARGEST_CITY (VARCHAR(30))      

FILE = C:\Users\Desktop\AA_PB_TBL_AUS_STATES.txt;  
      
SHOW;
BEGIN LOADING AA_PB_TBL_AUS_STATES ERRORFILES AA_Error1,AA_Error2
   CHECKPOINT 5;
INSERT INTO AA_PB_TBL_AUS_STATES
  
 (
 STATE_ID,
STATE,
ABBR,
CAPITAL,
LARGEST_CITY  

VALUES
(
:STATE_ID,
:STATE,
:ABBR,
:CAPITAL,
:LARGEST_CITY
); 
 
END LOADING;

LOGOFF

Accepted Solutions
Senior Apprentice

Re: Fastload query error - .Execute the query one query at a time

Hi,

 

Your Fastload output is not complete, all that show is that the FL program logged on to the TD system.

 

FYI - here is a complete, successful(-ish) FL output. Yes yours will be different in places, but it gives you an idea of what is written to the output:

     ===================================================================
     =                                                                 =
     =          FASTLOAD UTILITY     VERSION 16.10.00.01               =
     =          PLATFORM WIN32                                         =
     =          PID      1728                                          =
     =                                                                 =
     ===================================================================

     ===================================================================
     =                                                                 =
     =          Copyright 1984-2017, Teradata Corporation.             =
     =          ALL RIGHTS RESERVED.                                   =
     =                                                                 =
     ===================================================================

**** 12:32:57 Processing starting at: Tue Dec 04 12:32:57 2018

     /* dummy LGN file to allow the user script to contain the LOGON command */
     /* dummy DDB file to allow the user script to contain the LOGON command */
     /***********************************************************************
      Run simple Fastload
      ***********************************************************************/
0001 tenacity 1;

**** 12:32:57 Tenacity Enabled:  1 hour(s)

0002 .run file ..\awm_util_lgn.txt


     ===================================================================
     =                                                                 =
     =          Logon/Connection                                       =
     =                                                                 =
     ===================================================================

0003 .logon td1600h/awmutil,

**** 12:32:59 Teradata Database Release: 16.00.00.04
**** 12:32:59 Teradata Database Version: 16.00.00.04
**** 12:32:59 Number of AMPs available: 2
**** 12:32:59 Current CLI or RDBMS allows maximum row size: 64K
**** 12:32:59 Character set for this job: ASCII

**** 12:33:00 Warning: EOF on INPUT stream.

0004 .run file queryband.sql;


0005 SET QUERY_BAND = 'test=AWMFASTLOAD; ApplicationName=AWMTEST; Version=2.0;' 
     FOR SESSION;

**** 12:33:00 Command completed successfully

**** 12:33:00 Warning: EOF on INPUT stream.

0006 .run file ..\awm_demo_database.txt


0007 database awm_dev_db;

**** 12:33:01 Command completed successfully

0008 .if errorcode != 0 then .quit 8;

**** 12:33:01 FDL4800 Invalid FastLoad statement

**** 12:33:01 Warning: EOF on INPUT stream.


0009 DROP TABLE FL1_LOAD_ET
     ;

**** 12:33:01 RDBMS error 3807: Object 'FL1_LOAD_ET' does not exist.

0010 DROP TABLE FL1_LOAD_UV
     ;

**** 12:33:01 RDBMS error 3807: Object 'FL1_LOAD_UV' does not exist.

0011 BEGIN LOADING FL1
     
     ERRORFILES    FL1_LOAD_ET,
                   FL1_LOAD_UV
                   CHECKPOINT 100000;

**** 12:33:01 Session count 2 returned by the DBS overrides
              user-requested session count
**** 12:33:01 Number of FastLoad sessions connected = 2
**** 12:33:01 FDL4808 LOGON successful
**** 12:36:40 Number of AMPs available: 2
**** 12:36:40 BEGIN LOADING COMPLETE


0012 SET RECORD FORMATTED;

**** 12:36:40 Now set to read 'FORMATTED' records
**** 12:36:40 Command completed successfully


0013 DEFINE
           FILLER          (CHAR(02)),
           ORDER_NO        (CHAR(10)),
           RAND_SEQ_NO     (CHAR(10)),
           SEQ_NO          (CHAR(10)),
           DATA_1          (CHAR(30)),
           DATA_2          (CHAR(30)),
           DATA_3          (CHAR(10)),
           DATA_4          (CHAR(30)),
           INS_DATE        (CHAR(08)),
           UPD_DATE        (CHAR(08), NULLIF = '        ')
     
     file = awmmloadjobs.dat;

**** 12:36:40 FDL4803 DEFINE statement processed


0014 SHOW;

     FILE = awmmloadjobs.dat
     FILLER                           OFFSET =      0 LEN =     2 CHAR
     ORDER_NO                         OFFSET =      2 LEN =    10 CHAR
     RAND_SEQ_NO                      OFFSET =     12 LEN =    10 CHAR
     SEQ_NO                           OFFSET =     22 LEN =    10 CHAR
     DATA_1                           OFFSET =     32 LEN =    30 CHAR
     DATA_2                           OFFSET =     62 LEN =    30 CHAR
     DATA_3                           OFFSET =     92 LEN =    10 CHAR
     DATA_4                           OFFSET =    102 LEN =    30 CHAR
     INS_DATE                         OFFSET =    132 LEN =     8 CHAR
     UPD_DATE                         OFFSET =    140 LEN =     8 CHAR
     TOTAL RECORD LENGTH = 148


     ===================================================================
     =                                                                 =
     =          Insert Phase                                           =
     =                                                                 =
     ===================================================================

0015 INSERT INTO FL1
          (
           ORDER_NO,
           RAND_SEQ_NO,
           SEQ_NO,
           DATA_1,
           DATA_2,
           DATA_3,
           DATA_4,
           INS_DATE,
           UPD_DATE
          )
     VALUES
          (
           :ORDER_NO              (FORMAT '9999999999'),
           :RAND_SEQ_NO           (FORMAT '9999999999'),
           :SEQ_NO                (FORMAT '9999999999'),
           :DATA_1,
           :DATA_2,
           :DATA_3                (FORMAT '9999999999'),
           :DATA_4,
           :INS_DATE              (FORMAT 'YYYYMMDD'),
           :UPD_DATE              (FORMAT 'YYYYMMDD')
          )
     ;

**** 12:36:40 Number of recs/msg: 417
**** 12:36:40 Starting to send to RDBMS with record 1
**** 12:36:40 Sending row 1860
**** 12:36:40 Finished sending rows to the RDBMS

**** 12:36:40 Acquisition Phase statistics:
              Elapsed time: 00:00:00 (in hh:mm:ss)
              CPU time:     0 Seconds
              MB/sec:       N/A
              MB/cpusec:    N/A


     ===================================================================
     =                                                                 =
     =          End Loading Phase                                      =
     =                                                                 =
     ===================================================================

0016 END LOADING;

**** 12:36:40 END LOADING COMPLETE

     Total Records Read              =  1860
     Total Error Table 1             =  0  ---- Table has been dropped
     Total Error Table 2             =  0  ---- Table has been dropped
     Total Inserts Applied           =  1860
     Total Duplicate Rows            =  0

     Start:   Tue Dec 04 12:36:40 2018
     End  :   Tue Dec 04 12:36:40 2018

**** 12:36:40 Application Phase statistics:
              Elapsed time: 00:00:00 (in hh:mm:ss)


0017 LOGOFF;

     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 12:36:41 Logging off all sessions
**** 12:36:41 Total processor time used = '0.296875 Seconds'
     .        Start : Tue Dec 04 12:32:57 2018
     .        End   : Tue Dec 04 12:36:41 2018
     .        Highest return code encountered = '8'.
**** 12:36:41 FDL4818 FastLoad Terminated

On the space issue it is possible that you are right 'on the cusp' of running out of space.

 

The manual inserts into the table might work, but FL also creates two error tables, each of which require some space. As I said, if the row inserts are just under the space limit, then the FL error tables might push it over the edge.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
21 REPLIES
Enthusiast

Re: Fastload query error - .Execute the query one query at a time

Hi Again,

 

I just realised I should ask this question first.

Where does this script go?

Should I be running it in SQL Assistant or at the cmd prompt?

If it's the cmd prompt, how do I paste the whole script there in one go?

Thanks

Senior Apprentice

Re: Fastload query error - .Execute the query one query at a time

Hi,

 

Fastload is a separate piece of software from SQLA, so you'll need it installed on your pc.

 

To run this the easiest is probably:

- use notepad or similar to create a plain text file (e.g. fload.txt)

- paste your script into that file

- open a command prompt and CD to the folder where your text file is stored

type in the following and press enter: fastload <fload.txt >fload.log

 

That will run the fastload program, reading in fload.txt as the relevant commands and putting the output into fload.log.

 

The fastload manual will (probably) have an example of this.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Fastload query error - .Execute the query one query at a time

Thanks Dave,

So if I understand this correctly, I do need to run this using the cmd prompt and I paste the full code I've posted into Notepad & name it fload & save it as a .txt file.
 
I'm not clear on what you mean by "and CD to the folder where your text file is stored"?
What exactly do I type in to tell the cmd prompt to go to this file?
 
I would have this file on the C://Desktop location.
Also, where do I add the "fastload <fload.txt >fload.log" text.
 
I appreciate your help, I just need this last part to get this finished.
 
Thanks Peter
Senior Apprentice

Re: Fastload query error - .Execute the query one query at a time

Hi Peter,

 

The CD command is windows command prompt command 'change directory'.

The command is typically (assuming you're using a current windows version - it should work, try it):

CD /d C:\Users\your-user-name

(you shouldn't really need the '/d', but just in case...)

The 'fastload' command needs to be typed into your command prompt as well.

fastload <fload.txt >fload.log

Note that when you press ENTER after typing the above command, expect there to be no response until the fastload has ended. The output is being written to 'fload.log' which is just plain text so you can 'monitor' the job by looking at that file - but do not use Word (or probably wordpad) to look at the file, those programs lock the file. Use something like Notepad.

 

Try that and see what happens.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Fastload query error - .Execute the query one query at a time

Thanks Dave,

 

I'm not qjite there yet.

This my screenshot.

I hit Enter after the second row & wait a couple of minutes, then check the table but no records have been added.

I tried both with the d\ & without.

Any ideas?

Thanks again.

 

 

Fload_cmd_script.PNG

 

 

Highlighted
Teradata Employee

Re: Fastload query error - .Execute the query one query at a time

Dave's instructions assume you are starting from a Windows command prompt but your screenshot indicates fastload is already running. It should look more like this:

 

C:\Users\123456\>cd Desktop
C:\Users\123456\Desktop>fastload <fload.txt >fload.log
C:\Users\123456\Desktop>
Enthusiast

Re: Fastload query error - .Execute the query one query at a time

Thanks Fred,

 

No luck, here's my syntax again.

Is it complete like this?

 

Thanks

 

Fload_cmd_script_2.PNG

Senior Apprentice

Re: Fastload query error - .Execute the query one query at a time

Hi,

 

As per Fred's comment it looks like you have already started fastload (you can see the fastload banner with the version number).

 

Back to the start...

  1. Login to Windows
  2. Use the Windows key+R - this will bring up the 'run' box.
  3. Type in: cmd and then click 'OK' - this will open a command prompt with nothing running
  4. In the command prompt type: cd /d c:\users\windows-username-here\desktop
  5. In the command prompt type: fastload <fload.txt >fload.log

That should work for you.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Fastload query error - .Execute the query one query at a time

Thanks Dave,

I don't mind where I type the code, I just want a step by step of what to do to get this to work.

I'm still not clear on what you mean as you've said type 'this' & type 'that' on the cmd prompt.

Do you mean type the first part, hit enter & then type the second part & hit enter?
OR just type the lot on the same line?