Handling timestamp column during fast export

Tools
Enthusiast

Handling timestamp column during fast export

Hi,

I am getting an error while running fast export script for timestamp column as
**** 08:33:59 UTY8713 RDBMS failure, 5407: Invalid operation on an ANSI Datetime or Interval
value.

for the timestamp column in table.

Please let me know how to get through this.

thanks
11 REPLIES
Enthusiast

Re: Handling timestamp column during fast export



select current timestamp

from tablename..
Enthusiast

Re: Handling timestamp column during fast export

It doesn't help in giving a solution if we don't know what you are doing with that time stamp column in the fast export script.

Does this same SQL run in queryman ?

Are you doing some conversion / addition etc in the SQL on the timestamp column ? (most likely you are trying to add something but not using the right interval data type).

If that doesn't help, please post the relevant SQL. and describe the intentions of the SQL.
Enthusiast

Re: Handling timestamp column during fast export

SELECT

TOP 10

ACCT_NBR ||'|'||

acct_type_cd ||'|'||

acct_sub_type_cd ||'|'||

ent_curr_acct_sts_cd ||'|'||

acct_subsrptn_eff_dt ||'|'||

curr_srv_accs_nbr ||'|'||

srv_accs_id ||'|'||

orgnl_srv_dt ||'|'||

src_sys_orgnl_srv_dt ||'|'||

curr_subsrptn_sts_cd ||'|'||

CURR_IMSI ||'|'||

CURR_IMEI ||'|'||

PRD_CD ||'|'||

PRD_DESC ||'|'||

PRD_CAT_CD ||'|'||

blng_eff_dt ||'|'||

blng_end_dt ||'|'||

SUBSRPTN_STS_RSN_CD ||'|'||

SUBSRPTN_STS_RSN_DESC ||'|'||

entry_dt_tm (title '')

FROM SBP_PREPAID_SUBS_INFO;

 *** Failure 5407 Invalid operation for DateTime or Interval.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

After running the above in bteq, I'm getting the above error. The data type of the Column entry_dt_tm is TS. I'm getting the error for this column. Request you to please help me with the format.

Senior Apprentice

Re: Handling timestamp column during fast export

There's no automatic typecast for TIME and TIMESTAMP, you must do it explicitly:

...

SUBSRPTN_STS_RSN_DESC ||'|'||

CAST(entry_dt_tm AS VARCHAR(20)) (title '')
Enthusiast

Re: Handling timestamp column during fast export

Thank you so much. It did work.

Enthusiast

Re: Handling timestamp column during fast export

 *** Query completed. 22526460 rows found. One column returned.

 *** Total elapsed time was 3 seconds.

Using bteq, I'm trying to export about 22M records from the table to the flat file. It is running for more then 4 hours and the export to the flat file is still happening. The logs are rolling.

But does it take so much of time for the export to happen?

Enthusiast

Re: Handling timestamp column during fast export

Just the initial thought.How about your sessions? how many!!! Is it peak time?  You can think of fastexport too. 

Senior Apprentice

Re: Handling timestamp column during fast export

22M records is not that much, but it mainly depends on the overall size, 22M * 100 bytes or 22M * 10KB...

You should try to find the bottleneck:

BTEQ is single-threaded, so check if one CPU is running at 100%, if it's less it might be the network.

Also check the settings for RESPBUFLEN using .SHOW CONTROLS, if your network is reliable you can switch to the maximum:

.SET SESSION RESPBUFLEN MAX1MB

There's another setting, but it already defaults to ON:

.SET SESSION TWORESPBUFS ON

Enthusiast

Re: Handling timestamp column during fast export

Thanks a lot once again. After updating the below values, the export to the flat file got completed in about 4 hours for all 22527844 rows. Just wondering, if there are some additional changes that can be done, so that the export gets completed in less then 4 hours time.

.SET SESSION RESPBUFLEN MAX1MB

.SET SESSION TWORESPBUFS ON

Before making this changes yesterday, just to export .6M records it took nine hours.