Cannot view data for sequential file

Soporte Procalidad · Post by **Soporte Procalidad** » Mon Sep 13, 2010 11:52 am

Hi, I have an error when I want to view data from sequencial file stage:

##I IIS-DSEE-TOSH-00002 19:40:06(001) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 19:40:06(002) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 19:40:06(003) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 19:40:07(000) <main_program> Parameter specified but not used in flow: PRM_DIRECTORIOS
##W IIS-DSEE-TOSH-00049 19:40:07(001) <main_program> Parameter specified but not used in flow: PRM_SA_SISCART
##W IIS-DSEE-TOSH-00049 19:40:07(002) <main_program> Parameter specified but not used in flow: _APT_CONFIG_FILE
##W IIS-DSEE-TOSH-00049 19:40:07(003) <main_program> Parameter specified but not used in flow: DSProjectMapName
##I IIS-DSEE-TFSC-00001 19:40:07(009) <main_program> APT configuration file: E:/IBM/InformationServer/Server/Configurations/Un_nodo.apt
##W IIS-DSEE-TFIG-00176 19:40:10(000) <Sqn_MoCodres,0> Input buffer overrun at field "desc_gesti", at offset: 141
##W IIS-DSEE-TOIX-00154 19:40:10(001) <Sqn_MoCodres,0> Import warning at record 0.
##W IIS-DSEE-TOIX-00018 19:40:10(002) <Sqn_MoCodres,0> Import unsuccessful at record 0.
##W IIS-DSEE-TFIG-00176 19:40:10(003) <Sqn_MoCodres,0> Input buffer overrun at field "desc_gesti", at offset: 141
##W IIS-DSEE-TOIX-00154 19:40:10(004) <Sqn_MoCodres,0> Import warning at record 1.
##W IIS-DSEE-TOIX-00018 19:40:10(005) <Sqn_MoCodres,0> Import unsuccessful at record 1.
##W IIS-DSEE-TFIG-00176 19:40:10(006) <Sqn_MoCodres,0> Input buffer overrun at field "desc_gesti", at offset: 141
##W IIS-DSEE-TOIX-00154 19:40:10(007) <Sqn_MoCodres,0> Import warning at record 2.
##W IIS-DSEE-TOIX-00018 19:40:10(008) <Sqn_MoCodres,0> Import unsuccessful at record 2.
##W IIS-DSEE-TFIG-00176 19:40:10(009) <Sqn_MoCodres,0> Input buffer overrun at field "desc_gesti", at offset: 141
##W IIS-DSEE-TOIX-00154 19:40:10(010) <Sqn_MoCodres,0> Import warning at record 3.
##W IIS-DSEE-TOIX-00018 19:40:10(011) <Sqn_MoCodres,0> Import unsuccessful at record 3.
##W IIS-DSEE-TFIG-00176 19:40:10(012) <Sqn_MoCodres,0> Input buffer overrun at field "desc_gesti", at offset: 141
##W IIS-DSEE-TOIX-00154 19:40:10(013) <Sqn_MoCodres,0> Import warning at record 4.
##W IIS-DSEE-TOIX-00018 19:40:10(014) <Sqn_MoCodres,0> Import unsuccessful at record 4.
##I IIS-DSEE-TOIX-00193 19:40:10(015) <Sqn_MoCodres,0> No further reports will be generated from this partition until a successful import.
##W IIS-DSEE-TFRS-00051 19:40:10(016) <Sqn_MoCodres,0> Missing record delimiter "\r\n", saw EOF instead
##W IIS-DSEE-TOIX-00154 19:40:10(017) <Sqn_MoCodres,0> Import warning at record 83.
##I IIS-DSEE-TOIX-00163 19:40:10(018) <Sqn_MoCodres,0> Import complete; 0 records imported successfully, 84 rejected.
**VIEWDATA ROW LIMITER HIT**

I've configured one node because the file is small

kandyshandy · Post by **kandyshandy** » Mon Sep 13, 2010 12:22 pm

your format tab details do not sync. with the file you are trying to open.

"Missing record delimiter "\r\n", saw EOF instead "

Soporte Procalidad · Post by **Soporte Procalidad** » Mon Sep 13, 2010 1:26 pm

Hi I've already uploaded the table definition from sequecial file but it's not work.

kandyshandy · Post by **kandyshandy** » Mon Sep 13, 2010 1:38 pm

Check the FORMAT tab of sequential file stage and not Columns tab.

Soporte Procalidad · Post by **Soporte Procalidad** » Mon Sep 13, 2010 1:52 pm

I already check the format tab but the error persists

prakashdasika · Post by **prakashdasika** » Mon Sep 13, 2010 7:58 pm

What type of file are you reading - Fixed width/ Delimiter, do you have any double quotes in the data. did you define any record delimiter (Unix Newline/ DOS format). Post the particluars for the readers to understand please.

Soporte Procalidad · Post by **Soporte Procalidad** » Tue Sep 14, 2010 8:33 am

The situacion is as follows:
I have one old production environment and one new production environment. Both have same conditions: Windows 2003 Server, DataStage 8 and Oracle 9g with the same release in all systems.
I export the job and then import in the new environment, configure the parameter set, compile de job and run. The file is the same in both enviroments but in the new it doesn't work. I uploaded the table definition, format and column again but the error still.
The file is delimiter with tab, double quote in the data and has special characters like the tilde because my data is in spanish.

kumar_s · Post by **kumar_s** » Tue Sep 14, 2010 8:52 am

Check for the NLS settings. Refer your prevoius environment.
Is the column desc_gesti the last one?
If not, check for the value accross the column, might be bigger than the prescribed limit.

Soporte Procalidad · Post by **Soporte Procalidad** » Tue Sep 14, 2010 9:09 am

In any of the environments is installed NLS, the column desc_gesti is the last one and I extended the size for this column. When I upload the format for the file datastage set the delimiter in end and show this error when it runs:
Sqn_MoCodres,0: Field "desc_gesti" with 'delim=end' did not consume entire input, at offset: 212

kumar_s · Post by **kumar_s** » Tue Sep 14, 2010 9:15 am

Looks like the record delimiter is not set right.
What is the record delimiter in the current file? Is that a normal Windows file?
How did you transfer from one server to another?
Any chances for the delimter to get changed?
What is the data type of the last column?
Try making the last field to Varchar 255.
Or Varbinary.

kumar_s · Post by **kumar_s** » Tue Sep 14, 2010 10:32 am

What is the datatype and lenght that you had given for desc_gesti?
Sometimes, datastage just tricks for Veiw data and allows to run the job.
Just try runnding the job and see, if you are getting the same error.
What is your current record delimiter?

Soporte Procalidad · Post by **Soporte Procalidad** » Tue Sep 14, 2010 11:31 am

When I load the format from file:
Final delimiter = end
Record delimiter = UNIX newline
Delimiter = Tab
Quote = Double
General / Pad Char= #

Then, I upload columns from table definition and all columns are mapped varchar (255)
When I pick in view data it shows this error:

APT configuration file: E:/IBM/InformationServer/Server/Configurations/Un_nodo.apt
##W IIS-DSEE-TFIG-00184 19:23:25(000) <Sqn_MoCodres,0> Field "desc_gesti" with 'delim=end' did not consume entire input, at offset: 212

Then, I delete the final delimiter and I can view data and run the job, this aborted and show this error:

Ora_sa_siscar,0: The system(sqlldr SA_SISCART/sasiscartristap@ARISPROD.WORLD CONTROL=ora.4728.827000.0.ctl LOG=ora.4728.827000.0.log BAD=ora.4728.827000.0.log.bad SILENT=header PARFILE=ora.4728.827000.0.par) failed; see the log file for the Oracle specific message.

I check data but I don't find nothing strange

kumar_s · Post by **kumar_s** » Tue Sep 14, 2010 11:35 am

You were trying to read a Dos file with Unix format.
Try to give DOS as Record Delimiter.

There error that you getting is from teh Sql Loader.
Check the log file specified.

ora.4728.827000.0.logora.4728.827000.0.log

Soporte Procalidad · Post by **Soporte Procalidad** » Tue Sep 14, 2010 1:11 pm

you're right, when I saw I changed but the error still:

Ora_sa_siscar,0: The system(sqlldr SA_SISCART/sasiscartristap@ARISPROD.WORLD CONTROL=ora.6032.14000d.0.ctl LOG=ora.6032.14000d.0.log BAD=ora.6032.14000d.0.log.bad SILENT=header PARFILE=ora.6032.14
000d.0.par) failed; see the log file for the Oracle specific message.

I don't understand if the environment and file are the same, the configuration is the same, why does not run the job? I've tested all I know: uploaded table definition, columns, format, I could see the data but I could not run the job, especially considering that the job runs in the previus environment

kumar_s · Post by **kumar_s** » Wed Sep 15, 2010 8:24 am

As said earliar, check the bad records from ora.6032.14000d.0.log.bad or check the log file to see the exact error message.