How to resolve the "java.lang.NullPointerException" when using "BEGIN INDEPENDENT TRANSACTION" in a TIBCO Data Virtualization procedure ?

How to resolve the "java.lang.NullPointerException" when using "BEGIN INDEPENDENT TRANSACTION" in a TIBCO Data Virtualization procedure ?

book

Article ID: KB0070441

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions.

Description

The "BEGIN INDEPENDENT TRANSACTION" statement is used to start a new transaction that operates independently of the current transaction. This means that any changes made within the scope of this transaction will not affect the current transaction, and vice versa. It's particularly useful when you need to perform a series of operations and treat each operation as individual units.

For example, check the below code: 
PROCEDURE testProc(OUT num INT)
BEGIN

	BEGIN INDEPENDENT TRANSACTION
  	
		DELETE FROM /shared/new_folder/new_view;

		INSERT INTO /shared/new_folder/new_view
			(
			  rowId
			, rowNumber
			, day
			, YEAR
			, WW
			, abc
			, pqr
			, xyz
			)	
		SELECT
			  rowId
			, rowNumber
			, day
			, YEAR
			, WW
			, abc
			, pqr
			, xyz
		FROM /shared/new_folder/old_view
		;
	
		COMMIT;

		EXCEPTION 
		  ELSE
			...

			ROLLBACK; 		
			RAISE;
		END;
	
END
But it causes an error as below: 
ERROR [jetty thread pool-261305] 2024-01-02 02:47:24.849 -0800 Util - null
com.compositesw.common.script.ScriptException: A system exception has occurred.  
  at AnonymousProcedure (line 40)
  at AnonymousProcedure (line 11)
  caused by: com.compositesw.common.CompositeRuntimeException  [script-1900011]
at com.compositesw.server.script.ScriptInstance.handleException(ScriptInstance.java:1007) ~[csserver.jar:?]
at com.compositesw.server.script.ScriptInstance.execute(ScriptInstance.java:852) ~[csserver.jar:?]
at com.compositesw.server.script.ScriptInstance.invoke(ScriptInstance.java:192) ~[csserver.jar:?]
at com.compositesw.cdms.webapi.service.WProcResultImpl$InvokerThread.run(WProcResultImpl.java:760) ~[cswebapi-server.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: com.compositesw.common.CompositeRuntimeException
at com.compositesw.common.CompositeRuntimeException.rethrow(CompositeRuntimeException.java:143) ~[cscommon.jar:?]
at com.compositesw.server.qe.QueryEngine.generateQueryPlan(SourceFile:858) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.a(SourceFile:292) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.a(SourceFile:153) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.process(SourceFile:110) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.InsertLogicalPlanGenerator.buildLogicalPlan(SourceFile:86) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1129) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$b.a(SourceFile:1905) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.execute(SourceFile:458) ~[csqe.jar:?]
at com.compositesw.server.qe.QESqlRequest.execute(SourceFile:156) ~[csqe.jar:?]
at com.compositesw.server.script.util.ScriptCursorValue.execute(ScriptCursorValue.java:375) ~[csserver.jar:?]
at com.compositesw.server.script.steps.SQLStep.invoke(SQLStep.java:103) ~[csserver.jar:?]
at com.compositesw.server.script.ScriptInstance.execute(ScriptInstance.java:789) ~[csserver.jar:?]
... 5 more
Caused by: java.lang.IllegalStateException
at com.compositesw.server.qe.QueryEngine$a.a(SourceFile:2323) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$b.a(SourceFile:1946) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.generateQueryPlan(SourceFile:853) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.a(SourceFile:292) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.a(SourceFile:153) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.SubQueryPlanGenerator.process(SourceFile:110) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.InsertLogicalPlanGenerator.buildLogicalPlan(SourceFile:86) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1129) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$b.a(SourceFile:1905) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.execute(SourceFile:458) ~[csqe.jar:?]
at com.compositesw.server.qe.QESqlRequest.execute(SourceFile:156) ~[csqe.jar:?]
at com.compositesw.server.script.util.ScriptCursorValue.execute(ScriptCursorValue.java:375) ~[csserver.jar:?]
at com.compositesw.server.script.steps.SQLStep.invoke(SQLStep.java:103) ~[csserver.jar:?]
at com.compositesw.server.script.ScriptInstance.execute(ScriptInstance.java:789) ~[csserver.jar:?]
... 5 more
Caused by: com.compositesw.common.CompositeRuntimeException
at com.compositesw.common.CompositeRuntimeException.rethrow(CompositeRuntimeException.java:143) ~[cscommon.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:1857) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:1786) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.getFromNode(SourceFile:823) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:752) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.generateLogicalPlan(SourceFile:218) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1212) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1015) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$PlanOptimizer.call(SourceFile:2275) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$PlanOptimizer.call(SourceFile:2251) ~[csqe.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
... 3 more
Caused by: java.lang.NullPointerException
at com.compositesw.server.qe.logical.operator.Relation.a(SourceFile:113) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.operator.Relation.isStoredProcedure(SourceFile:96) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.operator.Relation.isStoredProcedureWithColumnReferences(SourceFile:117) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.operator.Join.isProcedureJoin(SourceFile:254) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.getFromNode(SourceFile:996) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:752) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.getFromNode(SourceFile:952) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.getFromNode(SourceFile:990) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:752) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.generateLogicalPlan(SourceFile:218) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:1834) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:1786) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.getFromNode(SourceFile:823) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.a(SourceFile:752) ~[csqe.jar:?]
at com.compositesw.server.qe.logical.LogicalPlanGenerator.generateLogicalPlan(SourceFile:218) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1212) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1015) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$PlanOptimizer.call(SourceFile:2275) ~[csqe.jar:?]
at com.compositesw.server.qe.QueryEngine$PlanOptimizer.call(SourceFile:2251) ~[csqe.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
... 3 more
Use the following resolution to address issues like the one discussed above. 

Issue/Introduction

This article outlines the steps/changes that need to be made to the code, to fix the NPE issue when using "BEGIN INDEPENDENT TRANSACTION".

Environment

All Supported Environments.

Resolution

The use of a Dynamic Cursor is needed.

When using "BEGIN INDEPENDENT TRANSACTION," a transaction is initiated that operates independently of the current transaction. Combining this with a dynamic cursor allows TDV to perform data retrieval operations within the scope of that particular transaction without affecting other transactions. This ensures data integrity and consistency, especially when dealing with concurrent data access scenarios. Also, any errors encountered during data retrieval operations are isolated within the transaction scope. This facilitates better error handling and rollback mechanisms, ensuring that data integrity is maintained in case of failures.

Below is the code which implements a Dynamic Cursor:
  
PROCEDURE testProc(OUT num INT)
BEGIN

	BEGIN INDEPENDENT TRANSACTION
  	
		declare q cursor(a varchar ,b int,c varchar,d varchar,e varchar,f varchar,g varchar,h varchar);
		open q for 'SELECT
			  rowId
			, rowNumber
			, day
			, YEAR
			, WW
			, abc
			, pqr
			, xyz
		FROM /shared/new_folder/old_view
		;
		DELETE FROM /shared/new_folder/new_view;
		INSERT INTO /shared/new_folder/new_view
			(
			  rowId
			, rowNumber
			, day
			, YEAR
			, WW
			, abc
			, pqr
			, xyz
			)	
		SELECT
			* from q
		;
	
		COMMIT;

		EXCEPTION 
		  ELSE
			...

			ROLLBACK; 		
			RAISE;
		END;
	
END

 
Powered by Wolken Software