"Getting empty buffer block for reading" message

[snorkel9]

Below is part of the session log file. The main error that is occuring is:

Getting empty buffer block for reading

This occurs immediatley after the Source Qualifier [SQ_CP_ACC_PROPERTY_TRN_ARCH_DTL]. The SQL override returns records in SQL but for some reason not within Informatica and it crashes.


================================================
READER_1_1_1> DBG_21434 Reader [SQ_CP_ACC_PROPERTY_TRN_ARCH_DTL]: Getting empty buffer block for reading
MASTER> PETL_24020

***** RUN INFO FOR TGT LOAD ORDER GROUP [1], SRC PIPELINE [1] *****
MASTER> PETL_24018 Thread [READER_1_1_1] created for the read stage of partition point [SQ_CP_ACC_PROPERTY_TRN_ARCH_DTL] has completed: Total Run Time = [552.041187] secs, Total Idle Time = [0.000000] secs, Busy Percentage = [100.000000].
MASTER> PETL_24024 Thread [TRANSF_1_1_1_1] created for the transformation stage of partition point [SQ_CP_ACC_PROPERTY_TRN_ARCH_DTL] has completed. The total run time was insufficient for any meaningful statistics.
MASTER> PETL_24024 Thread [TRANSF_1_1_1_2] created for the transformation stage of partition point [AGG_TOTAL_CASH] has completed. The total run time was insufficient for any meaningful statistics.
MASTER> PETL_24025 Thread [WRITER_1_1_1] created for the write stage of partition point(s) [DM_CASH, DM_CUSTOMER_ACCOUNT] has completed. The total run time was insufficient for any meaningful statistics.
MASTER> PETL_24021 ***** END RUN INFO *****

 

[snorkel9]

This problem is still persisting. I would like to supply some more information in the hope that it might help you with my problem.

There is a complicated SQL Override consisting of UNIONS and pseudo tables which when executed in SQLPLUS returns around 40,000 records after 30 minutes. In the mapping I am using Transaction Control transformations with used defined commits and the rows are treated as "Data Driven". When I start the workflow I monitor the mapping properties and after 30 minutes the mapping fails and there are no statistics generated i.e. Throughput(row/sec) remains 0 and the session log file has the following message

READER_1_1_1> DBG_21434 Reader [SQ_CP_ACC_PROPERTY_TRN_ARCH_DTL]: Getting empty buffer block for reading


When I remove the Transaction Control Transformations from the mapping and rerun the mapping, after 30 minutes records are read in from the SQ and the mapping finishes successfully.

Running my mapping in a test environment were the data volumes are much much smaller the mapping completes with success.

Any idea what is happening?

 

[blom0344]

I guess we are out depth in this forum concerning your problem. I can only make some guesses if this setup of your does run succesfully on a much smaller dataset.
Does your SQL override involve any order by clause? That may cause memory problems.
Did you run an explain plan on the SQL to see if it is handled in an efficient way?

Could you post the SQL itself for us to look at? Are you sure the DBA has not limited query time to a certain number of minutes?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[RahulGR]

Check the database connections for the source tables that you are using. There might be a chance that the database connections of the source are pointing to an instance other than which is required.