x
I'm sorry, but you have been denied access to edit this topic.
Help get this topic noticed by sharing it on Twitter Twitter, Facebook Facebook, or email.
Surf Games Spider
sad I’m frustrated
Surf Games Spider February 27, 2011 01:54

Drill-through issue in DQ Analyser

Why am I Unable to start drill-through?
Unable to start drill-through.
java.sql.SQLException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at com.ataccama.dqc.model.internal.modelimpl.PoolingDataSource$PreparedStatementDecorator.executeQuery(PoolingDataSource.java:517)
at com.ataccama.dqc.tasks.profiling.common.DrillDownManager.start(DrillDownManager.java:77)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance.run(ProfilingAlgorithmInstance.java:207)
at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(ComplexStepNode.java:57)
at com.ataccama.dqc.processor.internal.runner.Node.run(Node.java:123)
at java.lang.Thread.run(Unknown Source)

This is the error which I get when I run the plan in DQ Analyser to create a profile.
What sort of privileges should the user connecting to the database for drill through have? It works in case of certain users and does not work for some other users - even though they have the same privileges at Database level.
2 people have
this question +1
Reply

  • Martin Landa EMPLOYEE
    Martin Landa (Official Rep) February 28, 2011 10:33
    Hello,
    thanks for the report and nice description of the problem. We have probably found the cause and we'll try to repair it and update the version for download.
    Problem is with PROF_META table, where are saved metadata about profiling jobs with drill-through for "Drop obsolete profiles" functionality. When the job starts, it checks whether the table already exists and if it does, it will insert a record in it and if it doesn't, it will create the table first.
    Now the problem is in check of the table "existence", the PROF_META table in your case probably exists in another user schema, where your user have Select privilage, so the job won't create your own PROF_META table. Later it is going to insert a record into the table and it fails on it - exactly on that the table for you doesn't exist.

    Very similar problem can happen on another databases like MS SQL, when PROF_META table exists in a different database schema.

    As I said, we are going to fix it, but I am not sure when we will have an update. What you can easily do is to create the PROF_META table on your own for you. Connect to the database in DQ Analyzer under your user, open SQL Viewer just by clicking any table and paste following create table code instead of select:


    CREATE TABLE "PROF_META"
    ( "PID" VARCHAR2(64) NOT NULL ENABLE,
    "PUSER" VARCHAR2(255) NOT NULL ENABLE,
    "PFILE" VARCHAR2(255) NOT NULL ENABLE,
    "PSTAMP" NUMBER(38,0),
    "TPREFIX" VARCHAR2(128),
    "TNUM" NUMBER(38,0),
    "TRANGE" NUMBER(38,0),
    CONSTRAINT "PK_PROF_META" PRIMARY KEY ("PID"))


    This should create appropriate PROF_META table for you and in later jobs it will use it. Another fix might be creating a synonym for the table or dropping that table, but I think this solution should work for you.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel
  • Surf Games Spider
    happy I’m happy
    Surf Games Spider March 02, 2011 07:12
    Great! This works! Thanks
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel
  • Steve
    sad I’m frustrated
    Steve March 03, 2011 15:50
    I am getting a different error when trying to create drill through using MySQL. The initial message speaks of an incorrect string value in S_1, row 1. I see the table has been created.

    java.sql.BatchUpdateException: Incorrect string value: '\xEF\xBF\xBDme ...' for column 'S_1' at row 1
    at com.mysql.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:816)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
    at com.ataccama.dqc.model.internal.modelimpl.PoolingDataSource$StatementDecorator.executeBatch(PoolingDataSource.java:765)
    at com.ataccama.dqc.tasks.profiling.runtime.RollUpWrap.processDrillDown(RollUpWrap.java:455)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$InputRunnable.drillDown(ProfilingAlgorithmInstance.java:358)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$InputRunnable.access$400(ProfilingAlgorithmInstance.java:282)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance.run(ProfilingAlgorithmInstance.java:216)
    at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(ComplexStepNode.java:57)
    at com.ataccama.dqc.processor.internal.runner.Node.run(Node.java:123)
    at java.lang.Thread.run(Unknown Source)
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel
  • Marek Ovcacek EMPLOYEE
    Marek Ovcacek (Official Rep) March 07, 2011 09:06
    Hello,

    we have probably found the problem. It is related to your database encoding option. By default, DQ analyzer MySQL connections are set up with "characterEncoding=utf8". It is safest option, but sometimes causes trouble, if the database has different (non utf8) encoding, which is probably your case.

    Try to check your database encoding in your DB admin client (for Mysql probably phpMyadmin). Then you can override standard DQ analyzer connection option and set the encoding to right value.

    To override DB connect option, in DQ analyzer go to Databases, select (right click) your mysql database, select edit connection. In database connection dialog connection parameters check "by url" option which lets you edit Connection string. In connection string, there is option "characterEncoding=utf8". Set this option to your database encoding then click "finish" to save the changes.

    This overrides standard utf8 options and lets you write to drilltrough table. But it is recommended to use utf8 encoded database with standard options instead.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel
  • Steve
    sad I’m frustrated
    Steve March 07, 2011 17:03
    Thanks Marek. The db characterset is latin1 so I changed the Connection as you suggested and saved. I dropped the existing drill-through table. When I reran the profile I received a similar error (see below). Using utf8 resulted in more records written to the drill-through table than latin1 so I think there is definitely an encoding problem with the source data. Previous (successful) drill-throughs have be using United States-centric source data. This latest challenge has been using a larger global set of data.

    Is there a way to limit (select) the columns that are written to the drill-through tables?

    java.sql.BatchUpdateException: Incorrect string value: '\xCC?CHER...' for column 'S_1' at row 1
    at com.mysql.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:816)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
    at com.ataccama.dqc.model.internal.modelimpl.PoolingDataSource$StatementDecorator.executeBatch(PoolingDataSource.java:765)
    at com.ataccama.dqc.tasks.profiling.runtime.RollUpWrap.processDrillDown(RollUpWrap.java:455)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$InputRunnable.drillDown(ProfilingAlgorithmInstance.java:358)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$InputRunnable.access$400(ProfilingAlgorithmInstance.java:282)
    at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance.run(ProfilingAlgorithmInstance.java:216)
    at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(ComplexStepNode.java:57)
    at com.ataccama.dqc.processor.internal.runner.Node.run(Node.java:123)
    at java.lang.Thread.run(Unknown Source)
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel
  • Martin Landa EMPLOYEE
    Martin Landa (Official Rep) March 08, 2011 14:49
    Hello Steve,
    in case of profiling as you say global set of data I would recommend using utf8 encoding on a side of jdbc driver and database as well. Even if it might be possible to successfully run the job, the values in drill-through result will be garbled.
    I am not a big MySQL professional, but I have previously configured MySQL server installation in a following way for possibility to use it for drill-through with data in alphabets with accents, cyrillic or japanese characters.

    1) Create or alter your database, so its "CHARACTER SET" is utf8. You can do this in any mysql client or SQL Viewer in DQ Analyzer (right-click database under Databases node in File Explorer and choose Open in SQL Editor). Replace dbName with a name of your created database.

    alter database dbName CHARACTER SET utf8;

    2) List all variables which influence server and connection encoding:

    show variables like 'char%';



    Now set at least variables for client, connection, database and server to utf8 by "set =utf8;". Example:

    set character_set_client=utf8;

    I am not exactly sure, setting all those is necessary, but it won't cause any problem.

    3) Use the utf8 encoding in JDBC driver and set on the usage of unicode, by having ?characterEncoding=utf8&useUnicode=true in Connection string, like I have in a mine for connection to myserver:

    jdbc:mysql://myserver:3306/test?characterEncoding=latin1&useCursorFetch=true&defaultFetchSize=1000
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

    Cancel