Data Consumer Issues
This topic provides information to troubleshoot data consumer issues.
SQL Server Integration Services
Enterprise Single Sign-On
When using Enterprise Single Sign-On with SQL Server Integration Services, you may need to enter a placeholder value of MS$SAME for user name and password. Using Data Links, you can configure ESSO for use with SSIS.
In the Connection dialog, click Single sign-on for the Security method.
Select an Affiliate application from the drop-down list box.
In the All dialog, click Password and then click Edit Value. In the Edit Property Value dialog, enter MS$SAME for the Property Value, and click OK.
In the All dialog, click User ID and then click Edit Value. In the Edit Property Value dialog, enter MS$SAME for the Property Value, and click OK.
In the Connection dialog, click Test. You can view the results in the Microsoft Data Links dialog.
Click OK to save the configuration information.
FastLoad to insert TIMESTAMP value
When using SQL Server Integration Services OLE DB Destination with FastLoad to insert SQL Server datetime or datetime2 values into a DB2 TIMESTAMP column, you may encounter this error.
THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE
To work-around this problem, you should set the Data Provider data source property Use Early Metadata to true.
Use Early Metadata=True
Data Type Mapping
When using the SQL Server Integration Services Import and Export Wizards, from the Microsoft SQL Server Management Studio or Business Intelligence Design Studio, you can customize the default data conversions by editing XML mapping files. The Import and Export Wizard XML mapping files are located in the following folder.
C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles
To correctly map IBM DB2 for IBM i character and decimal data types to SQL Server data types, the data mapping files should be extended to include the DB2 data type long form synonym. For example, add the following data type mapping between DB2 INTEGER source and SQL Server. This mapping is compatible with SQLOLEDB, SQLNCL, SQLNCLI10, and System.Data.SqlClient.SqlConnection. It replaces the short form SourceDataType value INT with the long form INTEGER.
The following mapping for DB2 INT is compatible with a DB2 for z/OS V9 source.
<!-- INT -->
<dtm:DataTypeMapping>
<dtm:SourceDataType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
The following data type mapping for DB2 INTEGER is compatible with a DB2 for IBM i V6R1 source.
<!-- INTEGER -->
<dtm:DataTypeMapping>
<dtm:SourceDataType>
<dtm:DataTypeName>INTEGER</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
Data Type Mapping Files
The following table describes the three mapping files that you can edit when you use the Data Provider.
| DB2 Data Type Name | DB2ToMSSql | DB2ToMSSql10 | DB2ToSSIS10 |
|---|---|---|---|
| TIME | DATETIME | time | DT_DBTIME |
| TIMESTAMP | datetime | datetime2 | DT_DBTIMESTAMP2 |
| DATE | DATETIME | DATE | DT_DBDATE |
| CHAR | CHAR | CHAR | DT_STR |
| CHAR () FOR BIT DATA | BINARY | BINARY | DT_BYTES |
| CHAR () FOR MIXED DATA | NCHAR | NCHAR | DT_WSTR |
| CHAR () FOR SBCS DATA | CHAR | CHAR | DT_STR |
| CHARACTER | CHAR | CHAR | DT_STR |
| CHARACTER () FOR BIT DATA | BINARY | BINARY | DT_BYTES |
| CHARACTER () FOR MIXED DATA | NCHAR | NCHAR | DT_WSTR |
| CHARACTER () FOR SBCS DATA | CHAR | CHAR | DT_STR |
| NATIONAL CHARACTER | NCHAR | NCHAR | DT_WSTR |
| VARCHAR | VARCHAR | VARCHAR | DT_STR |
| VARCHAR () FOR BIT DATA | VARBINARY | VARBINARY | DT_BYTES |
| VARCHAR () FOR MIXED DATA | NVARCHAR | NVARCHAR | DT_WSTR |
| VARCHAR () FOR SBCS DATA | VARCHAR | VARCHAR | DT_STR |
| CHARACTER VARYING | VARCHAR | VARCHAR | DT_STR |
| CHARACTER VARYING () FOR BIT DATA | VARBINARY | VARBINARY | DT_BYTES |
| CHARACTER VARYING () FOR MIXED DATA | NVARCHAR | NVARCHAR | DT_WSTR |
| CHARACTER VARYING () FOR SBCS DATA | VARCHAR | VARCHAR | DT_STR |
| NATIONAL CHARACTER VARYING | NVARCHAR | NVARCHAR | DT_WSTR |
| LONG VARCHAR FOR BIT DATA | image | image | DT_IMAGE |
| LONG VARCHAR | text | text | DT_TEXT |
| GRAPHIC | NCHAR | NCHAR | DT_WSTR |
| VARGRAPHIC | NVARCHAR | NVARCHAR | DT_WSTR |
| GRAPHIC VARYING | NVARCHAR | NVARCHAR | DT_WSTR |
| SMALLINT | SMALLINT | SMALLINT | DT_I2 |
| INT | INT | INT | DT_14 |
| INTEGER | INT | INT | DT_14 |
| BIGINT | BIGINT | BIGINT | DT_18 |
| DECIMAL | NUMERIC | NUMERIC | DT_NUMERIC |
| NUMERIC | NUMERIC | NUMERIC | DT_NUMERIC |
| REAL | REAL | REAL | DT_R4 |
| FLOAT | FLOAT | FLOAT | DT_R8 |
| DOUBLE | FLOAT | FLOAT | DT_R8 |
| DOUBLE PRECISION | FLOAT | FLOAT | DT_R8 |
| BLOB | image | image | DT_BYTES |
| BINARY LARGE OBJECT | image | image | DT_BYTES |
| CLOB | text | text | DT_TEXT |
| CLOB () FOR MIXED DATA | ntext | ntext | DT_NTEXT |
| CLOB () FOR SBCS DATA | text | text | DT_TEXT |
| CHAR LARGE OBJECT | text | text | DT_TEXT |
| CHAR LARGE OBJECT () FOR MIXED DATA | ntext | ntext | DT_NTEXT |
| CHAR LARGE OBJECT () FOR SBCS DATA | text | text | DT_TEXT |
| CHARACTER LARGE OBJECT | text | text | DT_TEXT |
| CHARACTER LARGE OBJECT () FOR MIXED DATA | ntext | ntext | DT_NTEXT |
| CHARACTER LARGE OBJECT () FOR SBCS DATA | text | text | DT_TEXT |
| 130 | ntext | ntext | DT_NTEXT |
After you edit a mapping file, you must close and reopen the SQL Server Import and Export Wizard or the Business Intelligence Development Studio, depending on the environment in which you are working.
For more information about configuring SQL Server 2008 Integration Services, see the Importing and Exporting Data by Using the SQL Server Import and Export Wizard) topic in SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=193204).
Customizing Data Flow Components
You can use SQL Server Integration Services Data Flow Components to perform default and customized transformations. The customized transformations are based on developer-provided custom code.
The SQL Server Integration Services mapping files in XML format are for use with the Import and Export Wizard. These files are not for use with the Data Flow. SQL Server Integration Services offers a Pipeline Buffer class to allow enterprise developers to customize data mapping within the Data Flow. For more information about customizing data flow components using SQL Server 2008 Integration Services, see the Working with Data Types in the Data Flow topic in SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=241523).
SQL Server Replication
Derive Parameters
SQL Server Replication requires that Derive Parameter is set to FALSE.
Data Type Mapping
SQL Server Replication may convert data incorrectly, based on the default mappings from SQL Server to DB2 data types. We recommend that you review and revise the Replication data type mappings using the following SQL Server system stored procedures.
-
sp_helpdatatypemap -
sp_getdefaultdatatypemapping -
sp_setdefaultdatatypemappingFor more information, see the System Stored Procedures (Transact-SQL) topic in SQL Server Books Online (http://go.microsoft.com/fwlink/?LinkId=241524).
Problem with mapping SQL Server DATETIME2 to DBTYPE_TIMESTAMP
SQL Server 2008 Replication to DB2 for z/OS may fail with SQLCODE -188 (the string representation of a datetime value is not a valid datetime value). This occurs when Replication is configured to map DATETIME2 to DB2 VARCHAR(27) and uses subscription article commands with string literal data values.
Solution to problem with Step-by-Step Instructions
Re-configure SQL Server 2008 Replication to map DATETIME2 to DB2 TIMESTAMP and subscription article commands with parameters. This enables the Data Provider to format the DATETIME2 as a DB2 TIMESTAMP structure that is supported by the IBM DB2 database server.
Identify the data type mapping to modify. Use MASTER for all steps.
select * from
sys.fn_helpdatatypemap ( 'MSSQLSERVER', '%', '%', '%', '%', '%', 0 )Where destination_dbms = 'DB2' and source_type = 'datetime2'The results should indicate the mapping_id to modify. The following table shows the results pane for this example where the mapping_id is 189.
mapping_id source_dbms source_type destination_dbms destination_type destination_length 189 MSSQLSERVER datetime2 DB2 VARCHAR 27 Drop the data type mapping.
exec sp_dropdatatypemapping 189Add the data type mapping.
exec sp_adddatatypemapping @source_dbms = 'MSSQLSERVER', @source_type = 'datetime2', @destination_dbms = 'DB2', @destination_type = 'TIMESTAMP', @destination_nullable = 1, @destination_createparams = 0, @dataloss = 0, @is_default = 1Run the query again to verify the new data type mapping.
select * from
select * from sys.fn_helpdatatypemap ( 'MSSQLSERVER', '%', '%', '%', '%', '%', 0 )Where destination_dbms = 'DB2' and source_type = 'datetime2'
The results should indicate the mapping_id to modify. The following table shows the results pane for this example where the mapping_id is 189.
mapping_id source_dbms source_type destination_dbms destination_type destination_length 494 MSSQLSERVER datetime2 DB2 TIMESTAMP NULL Identify the replication subscription article to re-configure. Use the Transact-SQL USE statement to switch from the master database to the database from which you are replicating.
USE [Test]
select name, status from sysarticlesThe results should display the name of the article to modify. In this example, the following table shows the results where the name is DB2TS01.
name status DB2TS01 25 If the status value is 1 or 9, then the article is configured for string literal formatting.
If the status value is 17 or 25, then the article is configured for parameterized formatting.
Configure the replication subscription article for parameterized commands.
USE [Test]
DECLARE @publication AS sysname; DECLARE @article AS sysname; SET @publication = N'DB2TS_PUB01'; SET @article = N'DB2TS01'; EXEC sp_changearticle @publication, @article, 'status', 'parameters', 0, 0;For more information, see Replication System Stored Procedures Concepts (https://go.microsoft.com/fwlink/?LinkId=241525) in SQL Server Books Online.
Replication INSERT operation fails with Error 8152: String or binary data would be truncated
SQL Server Replication INSERT operation may fail with SQL Server Error 8152 (String or binary data would be truncated.). This may occur when (1) Data Provider is not configured to use early metadata, and (2) Replication is not configured to use parameterized INSERT statements.
Reconfigure Data Provider connection to specify "Use Early Metadata=true" in the SQL Server Replication subscriber data source definition.
For more information, see Configure Data Providers for DB2 Data Links (DB2) All Properties in OLE DB Provider for DB2 documentation.
Re-configure SQL Server Replication subscription article to include option "24" ("Includes the column name in INSERT statements and uses parameterized statements.").
For more information, see SQL Server Replication sp_addarticle (Transact-SQL) in SQL Server documentation.
SQL Server Analysis Services
When you design cubes for use with SQL Server Analysis Services, the tools generate SQL commands that contain long alias names that may exceed the maximum length supported by the DB2 server. Depending on the DB2 platform and version that you use, you may not be able to use queries with alias names exceeding 18 characters. For example, many objects deployed in DB2 for z/OS use names of 18 characters. Refer to the DB2 SQL Reference for your DB2 platform and version and check with your DB2 database administrator. We recommend that the administrator or developer update the two SQL Server Analysis Service configuration cartridge files that contain the data type mapping support for DB2 by changing the identifier-length (limit-table-identifier-length) from 29 to 18. The following are the names and location of the two cartridge files that must be updated.
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs
SQL Server Analysis Services uses the updated configuration files to correctly name objects in SQL commands.
SQL Server Migration Assistant for DB2
When using SQL Server Migration Assistant for DB2 (SSMA) to read DB2 CLOB data, you should define the DB2 connection within SSMA using the connection string option, and then include in the connection string the following argument.
- Use Early Metadata=True
This argument instructs the Data Provider to use early metadata (parameter and column data types) defined at design time or late metadata defined at runtime. This optional property accepts a Boolean value. The default value is false. Specify true when using SQL Server Migration Assistant (SSMA) for DB2 to read CLOB data, including schema information encoded as CLOB data (e.g. STATEMENT column of SYSIBM.SYSTRIGGERS table.