Its mentioned in the Help db, but searches in the Enterprise Integration forum and the Notes/Domino 6/7 and 4/5 forums come up empty.

(2)

After a month of working with various support organizations, including Lotus Support, I now have two ways to disable autocommit for DB2 LC LSX transactions.

Method 1:

'-- CommitFrequency property: specifies the number of modification actions between commits. 
'-- A value of zero causes a commit at disconnect; a value of one auto-commits after every action; 
'-- any other value commits after that many data modification actions.
Private Const COMMIT_FREQUENCY = 1 

'-- Used to set the LCConnection CommitFrequency property so that transactions are manually committed.
Private Const MANUAL_COMMIT = 0

'-- Set the DB2 Connector CommitFrequency property so that all transactions must be manually committed.
If ( con.LookupProperty ( COMMIT_FREQUENCY ) ) Then
	Call con.SetPropertyInt ( COMMIT_FREQUENCY, MANUAL_COMMIT )
End If

Method 2:

con.commitFrequency=0

I've used method 1, and it works! Who-hoo!

(0)

Roughly three weeks ago, I thought I had my problem with needing to rollback all previous LSX transactions solved. Using the ODBC2 LSX worked fine when I was testing with only one table. However, when my code attempts to update a second table, I get this error:

LSX Error: Status 12552
LSX Error: Err = 12552
LSX Error Line 133
LSX Error: Status Text = Error: Field mapping failed due to a missing field,  Connector 'odbc2',  Method -Insert-

I used the LCConnection.Catalog method to verify all of the fields in my destination table, and everything was correct (click on the Read More link below to see this code). When I switch my code to use the DB2 LSX instead, the code runs fine. However, the reason why I switched to the ODBC2 LSX was so that my agent would have the capability to perform a rollback in the event an error was encountered. Hmm. Guess I'll need to enter another Help Desk ticket for support.


(0)

I updated my code so that my DB2 table is cleared of all existing records through the use of this statement:

Call con.Action ( LCACTION_TRUNCATE )

However, when my code attempted to insert one new record into the table, I got this nasty LSX error:

LSX Error occurred in UPDATE_TABLE
LSX Error: Status 12325
LSX Error: Err = 12325
LSX Error Line 126
LSX Error: Status Text = Error: [IBM][CLI Driver][DB2] SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "00D70014", type of resource: "00000200", and resource name: "S001 .TABLE1 ". SQLSTATE=57011, Connector 'odbc2', Method -Insert- (-904)

Relatively befuddled, I began searching the internet for what the above could possibly mean. A google search on Unsuccessful execution caused by an unavailable resource and found this message reference:

SQL0904N

Unsuccessful execution caused by an unavailable resource. Reason code: "<reason-code>", type of resource: "<resource-type>", and resource name: "<resource-name>".

Explanation: The SQL statement could not be executed because resource "<resource-name>" of type "<resource-type>" was not available at the time for the reason indicated by "<reason-code>". Refer to the Problem Determination documentation of DB2 for MVS for an explanation of resource type codes.

Taking the DB2 InfoCenter's advice, I looked for more info about the "00D70014" Reason Code. Another Google search on Problem Determination documentation of DB2 for MVS helped me find my way to the DB2 for z/OS and OS/390 - DB2 Version 7 Library Page, where I used the "Search Version 7 BookManager Shelf" to search on "00D70014". This yielded the following:

00D70014

Explanation: An attempt to extend a data set failed, because a problem was detected in media manager services.

This reason code is issued by the following CSECT: DSNPXTN0

System Action: Abort the request.

User Response: Notify the operator, system programmer, or database administrator.

Operator Response: Notify the system programmer or database administrator.

System Programmer Response: Notify the database administrator if appropriate. Otherwise, check the packs available to the data set. They may merely be full or the data set may have reached its maximum allowable extents. For additional information, see the description of message DSNP001I.

Problem Determination: Obtain console sheet showing associated DSNP001I, DSNO007I, or DSNP011I messages and any related messages preceding them. This documentation will assist you in determining the cause of the problem. For additional information, see the description of these messages.

As it turned out, the database I was updating was out of extents, and needed to be moved to a new pack. Wow. I'll never make fun of cryptic DB2 error messages again!

(0)

Sure enough, changing the statement that initializes my LCConnection object from this:

Set con = New LCConnection ( "db2" )

to this:

Set con = New LCConnection ( "odbc2" )

did the trick! Calling the LCACTION_ROLLBACK actually worked! For right now, my code just inserts additional records into the table and rolls back the transactions at the end of my code, restoring the table to its original state. I now need to update my code so that it uses LCACTION_TRUNCATE to clear the table before inserting records.

(0)

A member of my customer's Notes application support team recommended that I use "odbc2" as the connector name for the new LCConnection, instead of "db2". I'm not sure why this would make a difference, but since using the LC LSX is like Alice and the looking glass at times, I'm willing to try anything.

(0)

I have an agent that uses LS:DO to insert records into a DB2 table. I now need to rewrite this agent so that it uses the DB2 LC LSX instead. The agent currently rolls back all transactions if an error is encountered:

  On Error Goto ErrorHandle
  ...
  con.AutoCommit = False
  con.CommitOnDisconnect = True
  ...
  con.Disconnect

ErrorHandle:

  con.RollbackTransactions
  result.Close ( DB_CLOSE )
  con.Disconnect

I'm trying to determine if the DB2 connector used on the Domino server supports rollback. I tried using the following code to determine if rollback is enabled:

  Call lcSession.LookupConnector ( "db2", connectorCode, identityFlagList )
		
  Print "connectorCode = " & connectorCode
  Print "identityFlagList.Text = " & identityFlagList.Text		
  Print "LCACTIDENTF_ROLLBACK = " & LCACTIDENTF_ROLLBACK
		
  Call identityFlagList.NumberListGetValue ( LCACTIDENTF_ROLLBACK, flagValue )

and this is what I get back:

3/8/2005 10:29:21 AM:  connectorCode = 65536
3/8/2005 10:29:21 AM:  identityFlagList.Text = 616, 31, 638, 20, 20
3/8/2005 10:29:21 AM:  LCACTIDENTF_ROLLBACK = 8
3/8/2005 10:29:21 AM:  LSX Error occurred in INITIALIZE
3/8/2005 10:29:21 AM:  LSX Error: Status 12291
3/8/2005 10:29:21 AM:  LSX Error: Err = 12291
3/8/2005 10:29:21 AM:  LSX Error Line 46
3/8/2005 10:29:21 AM:  LSX Error: Status Text = Error: Cannot locate list element

Since the ROLLBACK flag is 8, and the FlagList doesn't include 8, does this mean that ROLLBACK isn't supported by the DB2 LC LSX? Or, does this mean that I have to have rollback enabled at the DB2 level? Or did I just plain do this wrong?

(0)