ODBC Data Source for Match test

[xicheng_my_love]

As we know, when we design matches. We can configure a Test Environment to test our matches on sample data.

I have some questions for the ODBC Data Source:
1 Should the ODBC source be on client machine or server?
2 I have create a MySQL ODBC source on my client machine. And I also can see my ODBC source listed on drop-down menu of (Please select a DSN). But after I fill in everything and click Update, I get the following error message:
-------------------------------------------------------------------------------
##I IIS-DSEE-TFCN-00001 03:25:27(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.0.1.5183
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved



##I IIS-DSEE-TFCN-00006 03:25:27(001) <main_program> conductor uname: -s=AIX; -r=3; -v=5; -n=chevelle; -m=00C275CE4C00
##I IIS-DSEE-TOSH-00002 03:25:27(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 03:25:27(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 03:25:27(004) <main_program> orchstats: loaded
Resource bundle corresponding to message key return code {0} from SQLConnect(handle, {1}, ****, {2}) with message {3} not found! Check that DSHOME or APT_RESPATH is set.
##F IIS-DSEE-TDOD-00004 03:25:28(002) <main_program> Fatal Error: Could not connect to datasource?? (-1; root; test; [DataDirect][ODBC lib] Data source name not found and no default driver specified)
:
-------------------------------------------------------------------------------

I also find the following instruction in QualityStage User Guide:
5. In the Test Results Database area of the Configure window, follow these steps:
a. Select a source from the ODBC Data Source Name menu.
b. Type your user name and password for the results database.
c. Make the DSN available to the WebSphere DataStage server pointing at the results database.
d. Click Test Connection to test the validity of the connection before attempting to configure or run passes.

I think maybe I missed the following step:
c. Make the DSN available to the WebSphere DataStage server pointing at the results database.

How to complete this step?

Thanks!

[qt_ky]

Check out this IBM tech note:

http://www-01.ibm.com/support/docview.w ... wg21409481

It mentions DB2, Oracle, and SQL (meaning SQL Server; not mysql).

[stuartjvnorton]

You need to have the connection to the match test DB on both the client and server, and it has to have the same name on both.

To create the connection on the UNIX server, you need to create an entry for it on odbc.ini and uvodbc.config (in both the DSEngine directory and in the project directory of any project you intend to use that connection in).
You should be able to use the template entries in the 2 files to get you sorted out.

This is providing mySQL is supported...

[ray.wurlod]

And the user has to have sufficient privilege in the database to create and drop tables.

Ordinarily databases allow all privileges on owned tables - and I believe that this is the case with MySQL.

[qt_ky]

I believe that mysql ODBC connectivity is supported by the bundled Progress DataDirect ODBC drivers. I don't know if mysql is supported as a QualityStage Match Designer Database, since it is not mentioned in the tech note.

[xicheng_my_love]

Aha, I'm back.

Thanks for all you guys. I've almost got it.

Our DBA created a new DB2 DB and added to odbc.ini. I also created an ODBC with the same name on my Windows client. I filled all the field for creating a test environment, clicked 'Updated' and I got the prompt 'Test Enviroment update completed successfully'.

Now here the trouble goes. I created one Unduplicate Match Specification and clicked 'Test All Passes'. I got the following error:

##E IIS-DSEE-TDOD-00007 15:10:05(001) <APT_CombinedOperatorController,0> [DataDirect][ODBC DB2 Wire Protocol driver]String data, right truncated. Error in parameter 49.
##E IIS-DSEE-TFOR-00001 15:10:05(002) <ODBC_Enterprise_0,0> Failure during execution of operator logic.
##F IIS-DSEE-TDOD-00007 15:10:05(004) <APT_CombinedOperatorController,0> Fatal Error: {0}
##E IIS-DSEE-TFPM-00192 15:10:05(000) <node_node2> Player 11 terminated unexpectedly.
##E IIS-DSEE-TFPM-00338 15:10:05(000) <main_program> APT_PMsectionLeader(2, node2), player 11 - Unexpected exit status 1.
##E IIS-DSEE-TFSC-00011 15:10:10(000) <main_program> Step execution finished with status = FAILED.

Can anybody shed some light on this? Thanks

[xicheng_my_love]

Aha, I'm back.

Thanks for all you guys. I've almost got it.

Our DBA created a new DB2 DB and added to odbc.ini. I also created an ODBC with the same name on my Windows client. I filled all the field for creating a test environment, clicked 'Updated' and I got the prompt 'Test Enviroment update completed successfully'.

Now here the trouble goes. I created one Unduplicate Match Specification and clicked 'Test All Passes'. I got the following error:

##E IIS-DSEE-TDOD-00007 15:10:05(001) <APT_CombinedOperatorController,0> [DataDirect][ODBC DB2 Wire Protocol driver]String data, right truncated. Error in parameter 49.
##E IIS-DSEE-TFOR-00001 15:10:05(002) <ODBC_Enterprise_0,0> Failure during execution of operator logic.
##F IIS-DSEE-TDOD-00007 15:10:05(004) <APT_CombinedOperatorController,0> Fatal Error: {0}
##E IIS-DSEE-TFPM-00192 15:10:05(000) <node_node2> Player 11 terminated unexpectedly.
##E IIS-DSEE-TFPM-00338 15:10:05(000) <main_program> APT_PMsectionLeader(2, node2), player 11 - Unexpected exit status 1.
##E IIS-DSEE-TFSC-00011 15:10:10(000) <main_program> Step execution finished with status = FAILED.

Can anybody shed some light on this? Thanks

[rjdickson]

Hi,

Are you sure you created the Match Designer database as UTF-8 with 32k pagesizes?

[xicheng_my_love]

Hi rjdickson,

This is the command we used to create the DB2 DB:
db2 create db MatchT USING CODESET UTF-8 TERRITORY US PAGESIZE 32768

Hi,

Are you sure you created the Match Designer database as UTF-8 with 32k pagesizes?

[xicheng_my_love]

Hi rjdickson,

This is the command we used to create the DB2 DB:
db2 create db MatchT USING CODESET UTF-8 TERRITORY US PAGESIZE 32768

Hi,

Are you sure you created the Match Designer database as UTF-8 with 32k pagesizes?

[rjdickson]

Hi,

Not sure it will help, but here is the SQL I've used successfully. Also, have you set the 'Unicode' extended type on the output from Standardization? Make sure that extended type is set for all char/varchar columns, and is on the table definition you load into the match designer

Code: Select all

--------------------------------------------------------------------------------
-- QualityStage MatchDesigner test database creation for DB2 Version 9.5
--------------------------------------------------------------------------------
-- Copyright (c) 2007-2008 IBM Corporation. All rights reserved.
--------------------------------------------------------------------------------
--
-- See the accompanying readme for instructions on using this script.
-- 
-- The settings in this file are the one that are recommended for creating and 
-- configuring a DB2 9.5 database to be used as the QualityStage MatchDesigner
-- test database.
--
--------------------------------------------------------------------------------

-- CODESET:
-- ========
--   This creates a database with the recommended codeset of UTF-8. If the 
--   database is configured to use another codeset, data entered or transferred
--   that contains characters not supported by the configured codeset will not
--   be stored, processed or displayed correctly by QualityStage MatchDesigner
--   . To avoid issues with unsupported characters use the UTF-8 
--   codeset.
--
-- COLLATE:
-- ========
--   With the Identity collating sequence, strings are compared byte for byte.
--   Identity is the default for Unicode databases. The value of this property 
--   impacts the way strings are compared and sorted in a query. To ensure case 
--   sensitive query behavior, the Identity collation must be used. If a 
--   different collation is required for some reason, properties 
--   Query.Collator.Locale and Query.Collator.Strength must be set accordingly 
--   in the MDDB.bootstrap.properties file used to configure the matching 
--   behavior on the server side, so that any String sorting performed in memory
--   produce results compatible with String sorting performed by the SQL engine.
--
--   Recommendation: Use IDENTITY collation regardless of the CODESET to ensure 
--   consistent query behavior. 
--
-- AUTOCONFIGURE:
-- ==============
--   DB2 will automatically configure 25% of physical memory for the database
--   and the USERSPACE1 tablespace will automatically be configured to use 
--   automatic storage, with an initial size of 1GB

CREATE DATABASE @DATABASE_NAME@ ON '@INSTALL_ROOT@' 
       ALIAS @DATABASE_ALIAS@
       USING CODESET UTF-8 TERRITORY US 
       COLLATE USING IDENTITY
       DFT_EXTENT_SZ 2
       USER TABLESPACE
         MANAGED BY AUTOMATIC STORAGE
         AUTORESIZE YES
         INITIALSIZE 500 M
         INCREASESIZE 10 PERCENT
         MAXSIZE NONE;


-- QUERY_HEAP_SZ
-- =============
--   Query heap size. This parameter specifies the maximum amount of memory that
--   can be -- allocated for the query heap. It should be set to a value at 
--   least five times larger than aslheapsz. The default is 1000.
--
--   Recommendation: Set the value of QUERY_HEAP_SZ to 4096.

UPDATE DBM CFG USING QUERY_HEAP_SZ 4096 IMMEDIATE;
-- ASLHEAPSZ
-- =========
--   Application support layer heap size. The default is 15.
-- 
--   Recommendation: Set the value of ASLHEAPSZ to 1024.

UPDATE DBM CFG USING ASLHEAPSZ 1024 IMMEDIATE;

-- AGENT_STACK_SZ  
-- ==============
--   Agent stack size. The virtual memory that is allocated to each agent by 
--   DB2. The stack increases when the complexity of the query increases. The 
--   memory is committed when each SQL statement is processed. When preparing a 
--   large SQL statement, the agent can run out of stack space and the system 
--   will generate a stack overflow exception. When this happens, the server 
--   will shut down because the error is non-recoverable. The agent stack size
--   and the number of concurrent clients are inversely related: a larger stack
--   size reduces the potential number of concurrent clients that can be 
--   running. The default value is 64. 
--
--   Recommendation: Set the value of agent_stack_sz to 256.

UPDATE DBM CFG USING agent_stack_sz  256 IMMEDIATE;


-- STMTHEAP      
-- ========  
--   Statement heap size. If you have very large SQL statements and the database
--   manager issues an error (that the statement is too complex) when it 
--   attempts to optimize a statement, you should increase the value of this 
--   parameter in regular increments (such as 256 or 1024) until the error 
--   situation is resolved. The default value is 2048.
--
--   Recommendation: Set the value of STMTHEAP to 10240.

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING STMTHEAP 10240;

-- APPLHEAPSZ      
-- ==========
--   Application heap size. The value of this parameter must be increased if 
--   your applications receive an error indicating that there is not enough 
--   storage in the application heap. The default value is 256.
--
--   Recommendation: Set the value of STMTHEAP to 4096.

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING APPLHEAPSZ 4096 IMMEDIATE ;

-- DFT_MON monitor switches:
-- =========================
--   By default, all the DFT_MON switches are disabled except DFT_MON_TIMESTAMP.
--   All the switches will incur some sort of overhead - in particular, as CPU 
--   approaches 100%, DFT_MON_TIMESTAMP will have an increasing effect on 
--   performance.
--
--   Recommendation: Leave default for all DFT_MON switches (off), except 
--   DFT_MON_TIMESTAMP which should explicitly be disabled.

UPDATE DBM CFG USING DFT_MON_TIMESTAMP OFF IMMEDIATE;

-- AVG_APPLS
-- =========
--   Average number of active applications. Used by the query optimizer to help
--   estimate how much buffer pool will be available at run-time for the access
--   plan chosen.

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING AVG_APPLS 5 IMMEDIATE;

-- DBHEAP
-- ======
--   Database Heap.

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING DBHEAP 4096 IMMEDIATE ;

-- SELF_TUNING_MEM:
-- ================
--   New to v9, DB2 has the ability to self-tune a number of memory related 
--   parameters. These parameters are:
--
--    DATABASE_MEMORY
--    LOCKLIST 
--    MAXLOCKS 
--    PCKCACHESZ 
--    SHEAPTHRES_SHR 
--    SORTHEAP 
--    STMTHEAP (9.5)
--    DBHEAP (9.5)
--    APPLHEAPSZ (9.5)
--
--   By default, all these parameters are enabled for self tuning memory apart
--   from SHEAPTHRES_SHR.
--
--   Recommendation: Enable all parameters for self-tuning memory since this is 
--   an autonomic feature which should mean less intervention and administration
--   of the database is required.

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING SHEAPTHRES_SHR AUTOMATIC;

-- Logging configuration
-- =====================

UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING LOGBUFSZ 2048 IMMEDIATE;
UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING LOGFILSIZ 1000;
UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING LOGPRIMARY 50;
UPDATE DATABASE CONFIGURATION FOR @DATABASE_NAME@ USING LOGSECOND 200;

-- Granting all the required access rights to the MDDB user
-- ========================================================

CONNECT TO @DATABASE_NAME@;

GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT ON DATABASE TO USER @DATABASE_USERNAME@;
COMMIT WORK;

-- Bufferpools
-- ============
--   In DB2 9 bufferpools have the ability to be managed by the self-tuning 
--   memory feature. To be able to support installation in a variety of
--   environments this setting is used.
--   DB2 9.5 FP2 has a bug where the following line causes problems with report runs
--   DB2 9.5 FP3 will have this fixed, and these lines can be added to the below statement
--   after the 'SIZE 2048 AUTOMATIC' line
--       NUMBLOCKPAGES 2048 BLOCKSIZE 32 

CREATE BUFFERPOOL MDBUFFPOOL IMMEDIATE 
       SIZE 8000 AUTOMATIC 
       PAGESIZE 32 K ;

-- DMS Tablespace
-- ==============
--   Create a DMS tablespace with 32k pagesize to hold wide tables whose storage
--   is automatically managed by DB2

CREATE REGULAR TABLESPACE MDDBSPACE 
       IN DATABASE PARTITION GROUP IBMDEFAULTGROUP 
       PAGESIZE 32768 
       MANAGED BY AUTOMATIC STORAGE
       AUTORESIZE YES
       INITIALSIZE 500 M
       INCREASESIZE 10 PERCENT
       MAXSIZE NONE
       EXTENTSIZE 16
       BUFFERPOOL MDBUFFPOOL
       FILE SYSTEM CACHING  
       DROPPED TABLE RECOVERY ON;

COMMIT WORK;

-- SMS Tablespace
-- ==============
--   Create a temporary SMS tablespace for the MDDB regular tablespace whose 
--   storage is automatically managed by DB2

CREATE TEMPORARY TABLESPACE TEMPMDDBSPACE  
       IN DATABASE PARTITION GROUP IBMTEMPGROUP 
       PAGESIZE 32768 
       MANAGED BY AUTOMATIC STORAGE
       EXTENTSIZE 16
       BUFFERPOOL MDBUFFPOOL
       FILE SYSTEM CACHING;

COMMENT ON TABLESPACE TEMPMDDBSPACE IS '32 page swap';

COMMIT WORK;

CONNECT RESET;
TERMINATE;

[rjdickson]

Hi,

One more thought: Make sure you have the latest ODBC drivers for your version. If there are updates (and there are for 8.7), then go to http://www-933.ibm.com/support/fixcentral/...