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?
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
Solved! Go to Solution.
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.
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?
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.
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.
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.
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>
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...
That should work for you.
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.