loadMatView EXECUTE IMMEDIATE is truncating Insert SQL statement

[mtawk]

I am using Teiid embedded 10.2.1 with SQL server translator

I had a view that was not materializing without any raised exception or log error.

After investigating, we find out that the issue is coming from the stored procedure loadMatView located in SYSADMIN.sql.

The line below is truncating the SQL string to 4000 characters, and the insert statement for our view is larger than 4000 characters

                EXECUTE IMMEDIATE 'INSERT INTO ' || matViewStageTable || '(' || columns ||') SELECT '|| columns ||' FROM ' || schemaName || '.' || viewName || ' OPTION NOCACHE ' || schemaName || '.' || viewName;

We tried to put the insert statement SQL into a clob variable and it is still truncated on 4000

Your advice is appreciated to solve this issue.

Also, we noticed that some log contexts used in SYSADMIN.sql are not valid (org.teiid.MATVIEWS) or it should be org.teiid.PROCESSOR.MATVIEWS

        EXECUTE logMsg(context=>'org.teiid.MATVIEWS', level=>'WARN', msg=>e.exception);

[shawkins]

> We tried to put the insert statement SQL into a clob variable and it is still truncated on 4000

[TEIID-4376] EXECUTE IMMEDIATE should accept clob - JBoss Issue Tracker  and [TEIID-4378] Support clob concat - JBoss Issue Tracker do allow this in general, but with the way the dynamic sql is written the type will remain string - it won't automatically convert to a clob.  It would instead need to be cast('INSERT INTO' as clob) || ...  So an issue is needed to address that in the system procedures.

A workaround would be to adjust the maxStringLength system property - see System Properties · GitBook

> Also, we noticed that some log contexts used in SYSADMIN.sql are not valid (org.teiid.MATVIEWS) or it should be org.teiid.PROCESSOR.MATVIEWS

Yes that should be corrected as well.  Can you log something on that?

[mtawk]

We have already tried increasing maxStringLength but it did not solve the problem.

We tried your suggestion to put each part of the statement concatenation into a clob cast and it had solved the problem.

I have added a bug for the wrong log context: [TEIID-5641] Wrong Log Context in SYSADMIN.sql - JBoss Issue Tracker

[shawkins]

Also captured [TEIID-5644] materialization procedures can hit max string length - JBoss Issue Tracker to address the root issue.