473,722 Members | 2,293 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cursor Load fails with SQL0964C error

Env: DB2 UDB 8.2, AIX 5.3
While trying to load data (73 Million rows, Medium size table uses 4K
tablespace) into a table using cursor it fails with "SQL0964C The
transaction log for the database is full. SQLSTATE=57011"

But I felt that Load utility wont log any acvitities on the database.

Here it what I tried to do
db2 "declare c1 cursor for select CAST(PARTITIONI NG_NBR AS CHAR(10)),
VEH_IDENT_NBR, OPTN_CD, VIN_TYPE_CD, OPTN_INSTLT_TYP _CD,
UPDT_TXN_TYPE_C D, CAST(SRCD_DT AS VARCHAR(50)), DWH_EFCTV_TIMST M FROM
source_table"

db2 "load from c1 of cursor insert into target_table (PARTITIONING_N BR,
VEH_IDENT_NBR, OPTN_CD, VIN_TYPE_CD, OPTN_INSTLT_TYP _CD,
UPDT_TXN_TYPE_C D, SRCD_DT, DWH_EFCTV_TIMST M)"

Error :
SQL0964C The transaction log for the database is full. SQLSTATE=57011
Agent Type Node SQL Code Result
_______________ _______________ _______________ _______________ _______________ ___
LOAD 000 -00000964 Error. RESTART required.
_______________ _______________ _______________ _______________ _______________ ___
RESULTS: 0 of 1 LOADs completed successfully.
_______________ _______________ _______________ _______________ _______________ ___

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

Please advice
Thanks, db2udbgirl

Mar 20 '06 #1
3 7194
I've found out that it is becuase a tablespace is becoiming 100% full
hence it failed. But dont know as why it gave me a weird message that
Transaction log is full.

Thanks,db2udbgi rl

Mar 21 '06 #2
Load Does logging at the DELETE phase ...

This is from DB2 Manual

Load, during which the data is written to the table.
During the load phase, data is loaded into the table, and index keys
and table statistics are collected, if necessary. Save points, or
points of consistency, are established at intervals specified through
the SAVECOUNT parameter in the LOAD command. Messages are generated,
indicating how many input rows were successfully loaded at the time of
the save point. For DATALINK columns defined with FILE LINK CONTROL,
link operations are performed for non-NULL column values. If a failure
occurs, you can restart the load operation; the RESTART option
automatically restarts the load operation from the last successful
consistency point. The TERMINATE option rolls back the failed load
operation.

Figure 1. The Four Phases of the Load Process: Load, Build, Delete, and
Index Copy. While the load operation is taking place, the target table
is in the load in progress state. If the table has constraints, the
table will also be in the check pending state. If the ALLOW READ ACCESS
option was specified, the table will also be in the read access only
state.

Build, during which indexes are produced.
During the build phase, indexes are produced based on the index keys
collected during the load phase. The index keys are sorted during the
load phase, and index statistics are collected (if the STATISTICS YES
with INDEXES option was specified). The statistics are similar to those
collected through the RUNSTATS command. If a failure occurs during the
build phase, the RESTART option automatically restarts the load
operation at the appropriate point.

Delete, during which the rows that caused a unique key violation or a
DATALINK violation are removed from the table. Unique key violations
are placed into the exception table, if one was specified, and messages
about rejected rows are written to the message file. Following the
completion of the load process, review these messages, resolve any
problems, and insert corrected rows into the table.
Do not attempt to delete or to modify any temporary files created by
the load utility. Some temporary files are critical to the delete
phase. If a failure occurs during the delete phase, the RESTART option
automatically restarts the load operation at the appropriate point.

Note:
Each deletion event is logged. If you have a large number of records
that violate the uniqueness condition, the log could fill up during the
delete phase.
Index copy, during which the index data is copied from a system
temporary table space to the original table space. This will only occur
if a system temporary table space was specified for index creation
during a load operation with the READ ACCESS option specified.
Cheers....
Shashi Mannepalli

Mar 21 '06 #3
ok, Thanks for sharing this info.

Thanks,
db2udbgirl

Mar 22 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
10570
by: Brian Kelley | last post by:
I am trying to use threads and mysqldb to retrieve data from multiple asynchronous queries. My basic strategy is as follows, create two cursors, attach them to the appropriate databases and then spawn worker functions to execute sql queries and process the results. This works occasionally, but fails a lot taking python down with it. Sometimes it also loses connection to the database. Sometimes I get an error, "Commands out of sync; ...
6
19023
by: Matthew Houseman | last post by:
All, I've created a synonym that points to a package over a database link like so: CREATE SYNONYM API_MYLINK FOR USER.CSAPI_V2@INSTANCE.DOMAIN.COM I've granted execute like so: grant execute on CSAPI_V2 to scott; When I attach to the database in C# using ODP.NET and attempt to
2
10596
by: Paul Cheetham | last post by:
Hi, I am trying to load a custom cursor from my programs resources. I have successfully included the cursor resource, and I have confirmed that the name I am using is correct. Below is the code I am using to try and load the cursor - which always returns null. I have tried using LoadImage and LoadCursor with the same result.
5
10213
by: Paul Cheetham | last post by:
Hi, I have some custom colour cursors which I have added to my c# project, and set them to be compiled as "Embedded Resource" It seems impossible to load a colour cursor using the standard Cursor class, so I have had to resort to loading it using the Win32 API, and creating the cursor with - new Cursor(HCURSOR) The code I am using is shown at the bottom of this post.
10
10891
by: technocrat | last post by:
Hi, I am trying to declare and cursor and thn load from that cursor into another table. Since I have almost 4 million records, I cant do it without the cursor which reduces the time by almost 1/10th. I tried to create a sql statement for "load from cursor cur insert into table name" using java stored procedure, but this isnt recognised by sql since load isnt a sql keyword. So whats the solution to this. I have to do it programatically...
0
1415
by: babukgl | last post by:
Hi, We have a stored procedure which does a huge amount of transformation of data and it is running pretty slow. To avoid this we tried to create a cursor out of the SQL used in stored procedure & tried to load the data from that sql using Load from cursor statement. However, when we run this command i get an error message saying "NAME TOO LONG". Does this mean it has some limitation on the SQL query length used in cursor? Can i...
14
4440
by: peteh | last post by:
Hi All; We have many production jobs that "load from cursor" to a UDB/AIX 8.2 (with dpf) data warehouse from source tables residing Oracle 9i. Since Oracle dates are (roughly) equivalent to DB2 timestamps, we frequently use the date() function to "convert" from the Oracle date datatype to the DB2 date datatype. We have used this technique on over 20 Oracle tables for several months with no problem. One table in particular fails with a...
0
1635
by: Richard Lewis Haggard | last post by:
In an attempt to get around a resource leak issue, I tried to serialize a Cursor to a MemoryStream but the serialization fails with "Stock cursors cannot be serialized." This is puzzling since the Cursor being serialized is built from a bitmap and is not a stock cursor at all. Is this a misleading error message or am I actually somehow really serializing a stock cursor. Here's the code, pared down to its essentials. public Cursor...
1
5333
by: Ultrak The DBA | last post by:
UDB Version 8.2 FP7 (r"DB2 v8.1.1.128", "s061108", "U810098", and FixPak "14".) 64 bit running on AIX 5.3. We were getting the SQL0668 error below so we put the status sql into the script to see the table state right before the cursor/load ran. The table did not seem to be in a bad state. We switched this cursor/ load back to a regular export/load and the issue went away. We are doing several cursor loads on some 52Million row tables...
0
9236
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9154
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9087
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2601
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.