Teiid 9.0.0 - afterLoadScript giving exception in loadMatView

[mtawk]

I'm migrating from Teiid 8.11.3 to 9.0.0

I have changed the version column from integer to string in the materialization status table.

Actually i'm getting an exception in the procedure loadMatView on afterLoadScript execution for the view external materialization

I did not find in teiid log file where the exception is written via EXECUTE logMsg(context=>'org.teiid.MATVIEWS', level=>'WARN', msg=>e.exception);

I'm always getting FAILED_LOAD in the status table.

Here is a script of a simple external view:

CREATE VIEW "User" OPTIONS ( MATERIALIZED 'TRUE', UPDATABLE 'TRUE', "teiid_rel:MATVIEW_TTL" 0 , MATERIALIZED_TABLE 'implify_dataModel.implify_data.User', "teiid_rel:MATERIALIZED_STAGE_TABLE" 'implify_dataModel.implify_data.User_stage', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_STATUS_TABLE" '"imTeiidStatus"', "teiid_rel:MATVIEW_SHARE_SCOPE" 'VDB', "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE' , "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute implify_dataModel.native(''truncate table "implify_data"."User_stage"'');' ,"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User" RENAME TO "implify_data"."User_temp"'');execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User_stage" RENAME TO "implify_data"."User"'');execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User_temp" RENAME TO "implify_data"."User_stage"'');execute implify_dataModel.native(''truncate table "implify_data"."User_stage"''); SELECT ON_VIEW_LOADED_COMPLETE(''VIEW.B7F553FA-4F52-45FE-82BD-E2C18A4658B2.User'')  ' , "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM "imTeiidStatus" WHERE "Name"=''User'' AND "SchemaName" = ''implifyBusinessModel''' ) AS select "imUser"."FirstName" as "imUser_FirstName","imUser"."LastName" as "imUser_LastName" from  "~/h2db/implifyModel"."implify"."imUser" "imUser"

[shawkins]

Can you produce a debug or trace log around the time of the failure?  It's expected that the warn log should be there, but we should at least see what is happening around that time that may indicate what is happening.

[mtawk]

I tried to add to the exception part:  RAISE SQLEXCEPTION e.exception;

But i'm getting an error in the procedure incompatible types object and string.

Can you please tell me the synthax to write to get the exception message through RAISE SQLEXCEPTION?

[shawkins]

e.exception is of exception/object type, but RAISE SQLEXCEPTION expects a string for the message argument.  You want to do something like:

raise sqlexception 'something failed' chain e.exception;

[mtawk]

I wanted to know the exception message to know what is wrong in afterLoadScript, the same script worked fine in version 8.11.3.

I saw there were changes in afterLoadScript in version 9.0.0, in version 8.11.3 the script was split by semi-column and executed each part. In 9.0.0 it is executed one shot.

in which logger logMsg writes when having an exception in loadMatView ?

[shawkins]

> I saw there were changes in afterLoadScript in version 9.0.0, in version 8.11.3 the script was split by semi-column and executed each part. In 9.0.0 it is executed one shot.

That is [TEIID-4240] Anonymous procedure blocks should be used for materialization scripts - JBoss Issue Tracker - which simplifies the incorrect delimiting logic that was used in several places.

Even if the old style metadata is used, during load the logic will wrap that in a procedure block.  Is it possible that you are still using cached metadata?

> in which logger logMsg writes when having an exception in loadMatView ?

The server log using the org.teiid.MATVIEWS context at the warn level, or do you mean something else?

[mtawk]

The problem was coming from this part of afterLoadScript : SELECT ON_VIEW_LOADED_COMPLETE(''VIEW.B7F553FA-4F52-45FE-82BD-E2C18A4658B2.User'')

It is a virtual function created on our vdb that executes when materialization finishes but i don't know why it is not reachable in afterLoadScript

I have put the virtual function into ON_VDB_DROP_SCRIPT, and it is called successfully but 2 times at each materialization.

[shawkins]

> The problem was coming from this part of afterLoadScript : SELECT ON_VIEW_LOADED_COMPLETE(''VIEW.B7F553FA-4F52-45FE-82BD-E2C18A4658B2.User'')

What specifically is the problem?

> I have put the virtual function into ON_VDB_DROP_SCRIPT, and it is called successfully but 2 times at each materialization.

Do you mean that you are seeing multiple invocations, but only expect 1?

[mtawk]

The function ON_VIEW_LOADED_COMPLETE gives an exception when called into afterLoadScript while if put into ON_VDB_DROP_SCRIPT it is well called.

I was not able to get the exception message into loadMatView procedure, I looked into TeiidMatViews.log and no exception is written in there.

[shawkins]

Can you provide the full after load script that is failing?

[mtawk]

"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User" RENAME TO "implify_data"."User_temp"'');execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User_stage" RENAME TO "implify_data"."User"'');execute  implify_dataModel.native(''ALTER TABLE "implify_data"."User_temp" RENAME TO "implify_data"."User_stage"'');execute implify_dataModel.native(''truncate table "implify_data"."User_stage"''); SELECT ON_VIEW_LOADED_COMPLETE(''VIEW.B7F553FA-4F52-45FE-82BD-E2C18A4658B2.User'')  '

the call of the following function is causing the exception, but i did not find the log file where the exception is written

SELECT ON_VIEW_LOADED_COMPLETE(''VIEW.B7F553FA-4F52-45FE-82BD-E2C18A4658B2.User'')

if  I put the function into ON_VDB_DROP_SCRIPT, it is called successfully

[shawkins]

Thanks Mark.  This does look like an issue with TEIID-4240 such that the resolving is expecting all of the statements to return the same result set or use the no return option - but that should not be required to be consistent with the old behavior.  Can you log an issue for that?  And I'll also address why there isn't a log happening.

[mtawk]

New issue added : TEIID-4342

[shawkins]

This will be addressed for 9.0.2 which will be available today or Monday.