Error when using AdminShell to load external materialized view from source table
kako.hiroshi Jul 25, 2014 4:36 AMI have an Error which below when I used Adminshell to load date to external matelialized target table from source table with script.
*** The script I used is this ***
sql = connect("jdbc:teiid:UriageVDB@mm://CentOSv63:31000", "user", "user");
//sql.eachRow("select * from UriageModel.uriageView option nocache") { println "${it}" }
sql.execute("insert into MaterializedDB.MV_URIAGEVIEW select * from UriageModel.uriageView option nocache");
*** The error is this ***
[jboss@CentOSv63 teiid-adminshell]$ ./adminshell.sh load /home/jboss/work/test01.sql
======================================================================
Teiid AdminShell Bootstrap Environment
TEIID_HOME = /opt/JBoss/jboss-eap-6.1/dataVirtualization/teiid-adminshell
CLASSPATH = /opt/JBoss/jboss-eap-6.1/dataVirtualization/teiid-adminshell/lib/patches/*:/opt/JBoss/jboss-eap-6.1/dataVirtualization/teiid-adminshell/lib/teiid-adminshell-8.4.1-redhat-7.jar:/opt/JBoss/jboss-eap-6.1/dataVirtualization/teiid-adminshell/lib/*
JAVA = /usr/lib/jvm/java-1.7.0-openjdk.x86_64/bin/java
======================================================================
===> [import static org.teiid.adminshell.AdminShell.*; import static org.teiid.adminshell.GroovySqlExtensions.*; import org.teiid.adminapi.*;]
===> groovy.sql.TeiidSql@657f6c43
===> true
===> true
===> true
===> true
FATAL: org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 MaterializedDB: -4229 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "HIROSHI.KAKO"."MV_URIAGEVIEW" ("TOKUSAKICD", "TOKUSAKIMEI", "ADDRESS", "DEPARTMENT", "MS", "NAME", "DENPYONO", "PRODUCT", "PRICE", "AMOUNT", "TOTAL") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 MaterializedDB: -4229 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "HIROSHI.KAKO"."MV_URIAGEVIEW" ("TOKUSAKICD", "TOKUSAKIMEI", "ADDRESS", "DEPARTMENT", "MS", "NAME", "DENPYONO", "PRODUCT", "PRICE", "AMOUNT", "TOTAL") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:668)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:63)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:516)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:242)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:278)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:102)
at com.sun.proxy.$Proxy8.read(Unknown Source)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:377)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:525)
at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1031)
at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:312)
at groovy.sql.Sql.execute(Sql.java:1322)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:229)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at groovysh_evaluate.run(groovysh_evaluate:3)
at groovysh_evaluate$run.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at groovysh_evaluate$run.call(Unknown Source)
at org.codehaus.groovy.tools.shell.Interpreter.evaluate(Interpreter.groovy:67)
at org.codehaus.groovy.tools.shell.Interpreter$evaluate.call(Unknown Source)
at org.codehaus.groovy.tools.shell.Groovysh.execute(Groovysh.groovy:143)
at org.codehaus.groovy.tools.shell.Groovysh$execute.callCurrent(Unknown Source)
at org.codehaus.groovy.tools.shell.Shell.leftShift(Shell.groovy:114)
at org.codehaus.groovy.tools.shell.Shell$leftShift$0.call(Unknown Source)
at org.codehaus.groovy.tools.shell.commands.LoadCommand$_load_closure1.doCall(LoadCommand.groovy:80)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:88)
at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:273)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:886)
at groovy.lang.Closure.call(Closure.java:276)
at groovy.lang.Closure.call(Closure.java:289)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.callClosureForLine(DefaultGroovyMethods.java:2303)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:10556)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:10445)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:10476)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:10460)
at org.codehaus.groovy.runtime.dgm$134.invoke(Unknown Source)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:270)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at org.codehaus.groovy.tools.shell.commands.LoadCommand.load(LoadCommand.groovy:79)
at org.codehaus.groovy.tools.shell.commands.LoadCommand$load.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:44)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:143)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:151)
at org.codehaus.groovy.tools.shell.commands.LoadCommand.execute(LoadCommand.groovy:68)
at org.codehaus.groovy.tools.shell.Command$execute.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at org.codehaus.groovy.tools.shell.Command$execute.call(Unknown Source)
at org.codehaus.groovy.tools.shell.Shell.execute(Shell.groovy:101)
at org.codehaus.groovy.tools.shell.Groovysh.super$2$execute(Groovysh.groovy)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:88)
at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1058)
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodOnSuperN(ScriptBytecodeAdapter.java:127)
at org.codehaus.groovy.tools.shell.Groovysh.executeCommand(Groovysh.groovy:164)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce.invoke(PogoMetaMethodSite.java:266)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.callCurrent(PogoMetaMethodSite.java:51)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:151)
at org.codehaus.groovy.tools.shell.Groovysh.execute(Groovysh.groovy:112)
at org.codehaus.groovy.tools.shell.Groovysh$execute.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:44)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:143)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:151)
at org.codehaus.groovy.tools.shell.Groovysh.run(Groovysh.groovy:398)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSite.invoke(PogoMetaMethodSite.java:225)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.callCurrent(PogoMetaMethodSite.java:51)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:44)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:143)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:151)
at org.codehaus.groovy.tools.shell.Groovysh.run(Groovysh.groovy:371)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSite.invoke(PogoMetaMethodSite.java:225)
at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.call(PogoMetaMethodSite.java:63)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at org.codehaus.groovy.tools.shell.Main.main(Main.groovy:130)
at org.teiid.adminshell.GroovyAdminShell.main(GroovyAdminShell.java:30)
Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 MaterializedDB: -4229 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "HIROSHI.KAKO"."MV_URIAGEVIEW" ("TOKUSAKICD", "TOKUSAKIMEI", "ADDRESS", "DEPARTMENT", "MS", "NAME", "DENPYONO", "PRODUCT", "PRICE", "AMOUNT", "TOTAL") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:384)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:157)
at org.teiid.query.processor.relational.ProjectIntoNode.checkExitConditions(ProjectIntoNode.java:218)
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:129)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:157)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:139)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)
Caused by: org.teiid.core.TeiidException: -4229 Remote org.teiid.translator.jdbc.JDBCExecutionException: -4229 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "HIROSHI.KAKO"."MV_URIAGEVIEW" ("TOKUSAKICD", "TOKUSAKIMEI", "ADDRESS", "DEPARTMENT", "MS", "NAME", "DENPYONO", "PRODUCT", "PRICE", "AMOUNT", "TOTAL") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:242)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:76)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:345)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:312)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:113)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
... 6 more
Caused by: java.sql.SQLException: Remote com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.16.53] バッチが失敗しました。 バッチは実行されましたが、バッチの個々のメンバーで少なくとも 1 つの例外が発生しました。
getNextException() を使用して、バッチ処理された特定のエレメントの例外を取得してください。 ERRORCODE=-4229, SQLSTATE=null
at com.ibm.db2.jcc.am.fd.a(fd.java:467)
at com.ibm.db2.jcc.am.Agent.endBatchedReadChain(Agent.java:414)
at com.ibm.db2.jcc.am.uo.a(uo.java:5192)
at com.ibm.db2.jcc.am.uo.c(uo.java:4779)
at com.ibm.db2.jcc.am.uo.executeBatch(uo.java:3007)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1077)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:205)
... 14 more
I do not know why values part of insert statement would become ?,?,?,?,,, .
Another select SQL which is comment out one line above in the script worked without any problem and retuerned result set.
*** my environment is below ***
OS: Cent OS v6.3 on Virtual box in Window7 Pro 64bit
Java: 1.7.0_55
JBoss DS: 7.1.1.GA(Build id: GA-v20140314-2145-B688)
JBoss EAP: 6.1.1.GA (AS 7.2.1.Final-redhat-10)
Teiid Designer: 8.3.3
Does anyone tell me what is a wrong part of sql and how to write sql for insert statement?
any information would be appriciate