SQL0727N - Action Type - 5 Error
bala_e
I'm facing one strange problem. We are using DB2 UDB v 8.1.1 FP 9 (64
bit) in AIX.
We are into DW environment. We have the setup like, if users fetch
the data for the period of last 3 months, it will go and fetch from
table (To avoid more traffic to original table, we are populating this
table every day) other wise it will go and invoke the view against the
original tables. We are getting SQL0727N with Action Type 5 when the
user is running the report either in peak hours or more period like 1
year.
I've checked db2 ? SQL0727N and it says
**********************************************************************************************************
SQL0727N An error occurred during implicit system action type
"<action-type>". Information returned for the error
includes SQLCODE "<sqlcode>", SQLSTATE "<sqlstate>"
and message tokens "<token-list>".
Explanation:
The processing of a statement or command has caused the database
manager to implicitly perform additional processing. During this
processing an error was encountered. The action attempted is
shown by the "<action-type>":
1 implicit rebind of a package
2 implicit prepare of a cached dynamic SQL statement
3 implicit regeneration of a view
4 This return code is reserved for use by DB2.
5 incremental bind of a static SQL statement, not bound during
package bind time
6 implicit prepare of a reoptimizable statement containing
host-variables, special registers, or parameter markers
The sqlcode, sqlstate and message token list (each token is
separated by the vertical bar character) are provided. The
message tokens may be truncated. See the corresponding message
for the "<sqlcode>" for further explanation of the error.
The original SQL statement or command that caused the
"<action-type>" cannot be processed and the implicit system
action was not successful.
Federated system users: You may have received this message
because you dynamically prepared an SQL statement in a
pass-through session and then tried to execute the statement
after the session was closed.
**********************************************************************************************************
But every day we are doing rebind.
Can someone pls help me to solve out this problem?
With Regards
Balakrishnan Ethiraju
Knut Stolze
What's the full error message?
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
bala_e
> bala_e wrote:
> > Hi,
>
> > I'm facing one strange problem. We are using DB2 UDB v 8.1.1 FP 9 (64
> > bit) in AIX.
>
> > We are into DW environment. We have the setup like, if users fetch
> > the data for the period of last 3 months, it will go and fetch from
> > table (To avoid more traffic to original table, we are populating this
> > table every day) other wise it will go and invoke the view against the
> > original tables. We are getting SQL0727N with Action Type 5 when the
> > user is running the report either in peak hours or more period like 1
> > year.
>
> > I've checked db2 ? SQL0727N and it says
>
>
> > But every day we are doing rebind.
>
> > Can someone pls help me to solve out this problem?
>
> What's the full error message?
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Hi,
I'm getting the following error.
*******************************************************************************************************************************************
Message: ERROR [56098] [IBM] [DB2/AIX64] SQL0727N An error occurred
during implicit system action type "5" . Information returned for
the error includes SQLCODE "-1477", SQLSTATE "55019" and message
tokens "SESSION.BASE_QRY". SQLSTATE=56098
*******************************************************************************************************************************************
With Regards
Balakrishnan Ethiraju
Serge Rielau
db2 => ? SQl1477;
SQL1477N For table "<table-name>" an object "<object-id>" in table
space "<tbspace-id>" cannot be accessed.
Explanation:
An attempt was made to access a table where one of its objects is not
accessible. The table may not be accessible because of one of the
following reasons:
* The table had NOT LOGGED INITIALLY activated when the unit of work
was rolled back.
* The table is a partitioned declared temporary table and one or more
partitions failed since the temporary table was declared (all
declared temporary tables have the schema name SESSION).
* ROLLFORWARD encountered the activation of NOT LOGGED INITIALLY on
this table or a NONRECOVERABLE load on this table.
Access to this table is not allowed because its integrity cannot be
guaranteed.
User response:
One of the following actions can be taken.
* If the object is a table and it had NOT LOGGED INITIALLY activated,
drop the table. If this table is required, re-create it.
* If the object is a data partition, detach it from the table. If this
data partition is required, add a new one.
* If the object is a non-partitioned index, drop the index. If this
index is required, create a new one.
* If the table is a declared temporary table, drop the table. If this
table is required, declare it again.
* Otherwise, restore from a tablespace or database backup. The backup
image must have been taken subsequent to the commit point following
the completion of the non-recoverable operation (NOT LOGGED INITIALLY
operation, or NONRECOVERABLE load).
The catalogs can be used to determine what the object is. To determine
if the object is a table, use the following query:
SELECT TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID="<tbspace-id>" AND TABLEID="<object-id>"
If a table name does not appear as the result for the above query you
can determine if the object is a partition by using the following query:
SELECT DATAPARTITIONNAME, TABNAME
FROM SYSCAT.DATAPARTITIONS
WHERE TBSPACEID="<tbspace-id>" AND PARTITIONOBJECTID="<object-id>"
To determine if the object is an index, use the following query:
SELECT INDNAME
FROM SYSCAT.INDEXES
WHERE TBSPACEID="<tbspace-id>" AND INDEX_OBJECTID="<object-id>"
sqlcode: -1477
sqlstate: 55019
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
bala_e
> > Message: ERROR [56098] [IBM] [DB2/AIX64] SQL0727N An error occurred
> > during implicit system action type "5" . Information returned for
> > the error includes SQLCODE "-1477", SQLSTATE "55019" and message
> > tokens "SESSION.BASE_QRY". SQLSTATE=56098
>
> - Show quoted text -
Hi Serge,
Thanks for your reply. Actually when we are executing SP receiving
this error (Inside SP only we declared temp tables and accessing).
Inside the sp we are doing the following things,
<<Declaration of Temp table>>
<<Insert into Temp table>> --> Huge amount of data will be inserting.
It will take around 60 - 0 mins.
<<Processing Temp table records>>
<<final cursor return>>
So Is it possible the connection will go off between the statements
inside SP ? If so, pls advice us how to overcome this situation?
With Regards
Balakrishnan Ethiraju