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

Database
Senior Apprentice

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

Let's try this. The bits in bold are what you actually type, everything else is additional information for you.

 

Back to the start...

  1. Login to Windows
  2. Use the Windows key and the R key - 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  Remember to replace 'windows-username-here' with your windows username.
  5. In the command prompt type: fastload <fload.txt >fload.log

The following screen shots might help - note that 'DWellman' is my windows username.

This is the 'run' box (#2 above) with the appropriate command typed in (#3 above).

windows-run.png

Clicking 'OK' in the above picture will open a Windows command prompt.

This is the command prompt with the 'CD' command typed (#4 above) - again 'DWellman' is my windows username, change it to yours.

command-prompt.png

After typing in the above, press the 'enter' key, which will take you to:

command-prompt2.png

Now type in the command to run fastload (#5 above) and then press the 'enter' key.

start-fastload.png

 

That should allow you to do what you want.

 

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,

That's very clear.

I'll give that a try.

Enthusiast

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

Hi Dave,

I gave it a try, it ran but didn't add any data to the table.

The log file advises the below.

When I click on the Fastload App it opens for a second & dissappears agin.

 

**** 16:40:34 FastLoad cannot start. Please check CLIv2 and PIOM
              dependent libraries

I think you're getting close to finding the problem.

 

Where to from here?

 

Thanks Peter

Senior Apprentice

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

Hi,

It looks like Fastload is not installed properly (CLIv2 and PIOM are dependencies for FL).

Your best bet is to re-install Fastload (you shouldn't need to uninstall first) and make sure that you install any dependencies that it asks for - although those should be automatically selected. Thinking about it, I think the only dependency that shows up in the installer is 'CLIv2', so that bit should be easy.

Cheers,

Dave

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

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

When installing Teradata client software, don't directly execute a setup.exe from the individual product folders. Use the ...\TeradataToolsAndUtilitiesBase\TTU\setup.exe (or the ...\TTUExpress\TTU_Base.exe depending on which package you are using) and select the products you want.

Enthusiast

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

Thanks Dave,

I had the chance to reinstall everything & have tried again.

Here's the output;

 

    ===================================================================
     =                                                                 =
     =          FASTLOAD UTILITY     VERSION 16.20.00.02               =
     =          PLATFORM WIN32                                         =
     =          PID      8900                                          =
     =                                                                 =
     ===================================================================

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

**** 12:34:29 Processing starting at: Thu Dec 06 12:34:28 2018

0001 SESSIONS 4;

**** 12:34:29 FDL4866 SESSIONS command accepted

0002 RECORD 1 THRU 100;

**** 12:34:29 Starting record number set to  : 1
**** 12:34:29 Ending record number set to    : 100

0003 ERRLIMIT 25;

**** 12:34:29 Error limit set to: 25

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

0004 LOGON tdpid/User,

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

 Unfortunately, due to a user constraint, I can't check the table to see if it has worked.

 

I don't see any errors, so do you think this was successful?

 

Thanks Peter

Enthusiast

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

Thanks Fred,

I didn't have an option before, but now I've got the whole TTU.

Hopefully, this will now work.

Thanks Peter

Enthusiast

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

I've just checked now that I have access and it hasn't worked.

The error though is that there isn't enough room for the table in the DB.

So maybe it is working?

Any thoughts Dave or from anyone following this post?

 

Edit added, I've just tries the Insert using the File >> Import >> browse to the file & import & it completes the task OK.

So there's still a problem with the Fastload process??

 

Thanks Peter  

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
Highlighted
Teradata Employee

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

One other suggestion (not a solution, just an aid to debugging) - I think you are currently capturing only the "stdout" and not the "stderr" in the log file. Change the command you use to invoke FastLoad from just

    fastload <fload.txt >fload.log

to

    fastload <fload.txt 2>&1 >fload.log

so the log will include the error message(s) if something fails.