Tips to fixing Oracle Connections
- DSP requires that the full Oracle Admin client should be installed. Customers often will install a different version and will only upgrade when prompted. Oracle connections will work with Oracle Runtime. Oracle Thin client will only work via ODBC.
- Connection String settings For ODBCORACLE, OLEORACLE and Oracle
Oracle: Enter the database in server address and database in CranSoft, Enter userid and password in the correct fields. Test connection should pass.
OLE Oracle: Do the same as Oracle but create a connection string as:
Provider=ORAOLEDB.Oracle;Data Source=BOAORA1; User ID=cransoft_admin; Password=w3b4Xing; Fetchsize=10000;
NOTE: Fetchsize is not required but it can help performance. It can ranges from 100 to 10000.
ODBCOracle: Do the same as Oracle but create a connection string as:
DSN=QAOracleDS; UID=cransoft_admin;PWD=w3b4X1ng
If the default domain does not match, enter database as TNSNAMES entry followed by “.” and the domain name. If CompanyA bought out CompanyB, they will normally have different domains to get to both servers until the merge has been completed.
IN SQLNET.ORA file look for:
DEFAULT.DOMAIN= World or DEFAULT.DOMAIN= CompanyA.com
TNSNAMES.ORA look for:
PSAP =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP
(HOST = OraProdSAP.CompanyA.com)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = PSAP) ) )
OraSAPPR.CompanyB.com =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = OraSAPPR.CompanyB.com)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = PR1) ) )
For OLE or Oracle Connections, use TEST for the database if it is in the default domain.
Use ORALegacy.CompanyB.com for the database and use the same value for Data Source in an OLE Connection string if the value does not match the default domain.
Example: Provider=ORAOLEDB.Oracle;Data Source= ORALegacy.CompanyB.com; User ID=cransoft_admin; Password=w3b4Xing; Fetchsize=10000; - Oracle Error ORA-00942 is usually when the schema owner was entered incorrectly at the Target Source or the user id does not have access to the table. Check to make sure all elements were entered in UPPER CASE. (Schema owner and table name). In Toad, Users will enter the table name in mixed case and it works fine. Collect will wrap DOUBLE QUOTES around it to support SAP special table names like \APO\MATTYPE.
- Do a PATH command to verify Oracle is installed. It should be part of the path and make sure two Oracle Paths are not there. Normally it is at the beginning of the Path.
- Verify IUSR and IIS_USERS has READ and EXECUTE permission on the Oracle folder and push permissions down to all sub-folders.
- Verify TNSNAMES.ORA and SQLNET.ORA are available.
Edit TNSNAMES.ORA to see the setting.
TEST =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = bosdb-dv-cs.cransoft.com)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = TEST) ) )
ORALegacy.OldCompany.com =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = bosdb-dv-cs.cransoft.com)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = TEST) ) ) - Verify SQLNET.ORA is there and see if it is set to use TNSNAMES or LDAP.
Cransoft or DSP software cannot connect via Onames or EZConnect
Example: NAMES.DIRECTORY_PATH=( Tnsnames, Ldap, Onames, EZConnect)
Example: Default Domain is usually =WORLD but if set to other values, It needs to be part of the connection string. If Domain is set to URcompany.com, set Database= SAP. URcompany.com and Data Source=SAP. URcompany.Com in the connection string.
- Windows 2012 SQLNET.ORA needs a change after the default install is done.
Windows 2012 use this setting=SQLNET.AUTHENTICATION_SERVICES= (NONE)
Windows 2008 or 2003 wants this setting=SQLNET.AUTHENTICATION_SERVICES= (NTS) - From Command Prompt: TNSPING <TNSNAME>, Verify 32-bit is displayed.
If 64-bit is displayed, please uninstall the driver and reinstall or change the path if client_1 exists.
Write down the version. Oracle 10.0.2.0 does not support DTS. Only Cranport will work.
If TNSPING is not found, it means the Oracle Admin version is not installed.
Oracle 10.0.2.3 or 10.0.2.4 will work with Unicode characters and DTS
Oracle 10 can read the following Oracle versions 8, 9, 10, 11 and 12
Oracle 11.0.1.7 will work with Oracle 9, 10, 11 and 12
TNS PING Example:
- From Command Prompt: If TNSPING is not found, verify App server can communicate to the HOST by performing a PING hostname.
If HOST does not ping, it is probably a fire wall issue or the wrong address has been entered.
If Oracle THIN Client is installed, TNSPING will not work and only the ODBCOracle connection will work. ODBCOracle connection type will have issues with unicode characters.
Failed Ping Example:
- SQLPLUS UserID/Password@TNSNAME
Verify the User id and Password will work with the Oracle database.
If the default domain name does not match the TNSNAME entry in TNSNAMES.ORA file, then TNSNAME.DOMAIN_NAME must be entered.
- Within SQLPLUS: Perform a SELECT COUNT(*) FROM “SCHEMAOWNER”.”TABLENAME”
This will test if the User id has access to the table and returns the record count.
- Within SQLPLUS: DESC SCHEMAOWNER.TABLE
Describe Oracle command will show the columns with data types in the table if columns are not downloading.
- Oracle normally makes all table names and schema names in upper case. The double quotes around the schema name and table name make the process case sensitive. If a table is in mixed case, it must be entered this way on collect to download.
SAP is always UPPER case for schema owner, table name and column names.
Oracle Financials is always UPPER case for schema owner, table name and column names.
SAP Informix is the only database that breaks this rule where transparent tables are all lower case for schema owner, table name and column name.
- Use SSIS/DTS to test SQL access and review download columns process.
DTS packages can help try SQL and see a list of tables that the Userid has access to.
It can run the SQL to see the column list for that table collect is reading.
Download Column SQL is stored in Collect and this process can be used for any type of connection if the DTS packages are available.
SQL to look for oracle columns, REPLACE #SOURCEDBO# and #SOURCETABLE#
SELECT TABLE_NAME AS TableName, OWNER AS SchemaOWNER, SEGMENT_COLUMN_ID AS COLUMNORDER, COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHAR_LENGTH AS DataLength, DATA_PRECISION AS DataPrecision, DATA_SCALE AS DataScale, TO_CHAR(DECODE( NULLABLE, 'N', '0', '1')) as DATANULLABLE
FROM SYS.ALL_TAB_COLS WHERE OWNER = '#SOURCEDBO#' and TABLE_NAME = UPPER('#SOURCETABLE#')
ORDER BY SEGMENT_COLUMN_ID asc, 1 ASC, 2 ASC
Example SQL Using schema ORDDATA and table name = ORDDCM_DOC_TYPES:
SELECT TABLE_NAME AS TableName, OWNER AS SchemaOWNER, SEGMENT_COLUMN_ID AS COLUMNORDER,
COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHAR_LENGTH AS DataLength, DATA_PRECISION AS DataPrecision,
DATA_SCALE AS DataScale, TO_CHAR(DECODE( NULLABLE, 'N', '0', '1')) as DATANULLABLE
FROM SYS.ALL_TAB_COLS WHERE OWNER = 'ORDDATA' and TABLE_NAME = UPPER('ORDDCM_DOC_TYPES')
ORDER BY SEGMENT_COLUMN_ID ASC, 1 ASC, 2 ASC - From Command Prompt: Verify the App server has been rebooted since the Oracle Install.
Go to Command Prompt and enter systeminfo or systeminfo > c:\temp\Systeminfo.txt
Look at the System boot time and compare to the creation date on the oracle Folder.
- LDAP Issue can be hard to fix because the settings are not stored locally.
Collect received random connection errors when trying to download data.
Support had to create a local TNSNAME entry named DANORA1 and point it to the Oracle Database, Host and Port. Collect table downloads started working all the time. The Oracle DBA saw the test and realized that there was a problem on one of the LDAP servers. DANORA1 was not found on any LDAP server so the TNSNAMES.ora entry had to be used to resolve the connection. - Oracle Home in the control Panel
Oracle 8 & 9 sometimes requires ORACLE_HOME and ORACLE_SID set in the Control Panel
Control Panel\System and Security\System
Advance Settings -> Click on Environment Variables
Go to System Variables and see if ORACLE_HOME and ORACLE_SID are set - Can 64-bit and 32-bit be installed?
Yes (but it can cause issues) - The 32-bit driver must be in the path before the 64-bit driver. Oracle 32-bit driver usually has client_1 in the name of the path before the bin directory.
Typically, the install will put the 64-bit driver in the path first since the server OS is 64-bit.
It normally will have client _2 in the path. It is preferred not to have both versions installed.
- Can Oracle 9 client and Oracle 11 client both be installed?
Yes – We had a client that had an Oracle 7 Source but an Oracle 11 Target SAP System.
Cransoft support had to show the DBA how to configure the ORACLE_HOME (Control Panel) and system path to get the Source Oracle 7 downloaded. Then the user had to switch the Path and Oracle_Home (Control Panel) to get the Oracle 11 Target SAP Data downloaded. It was a crazy process but it worked for the client. - REGEDIT command: Multiple Oracle Homes in the Registry -
Multiple Oracle homes can cause issues. If changing the System Path and Control Panel options does not work, then I instruct the user to DELETE all oracle install folders, reboot the app server, and delete the Oracle install folder again because some of the DLLS might be in memory. Finally, go to the Registry and FIND all ORACLE_HOME and remove them as well. Then the user can install the new 32-bit driver.
- How to tell if Oracle OLE is installed. The OLE dll is not always installed.
Open up the DOS command prompt and type PATH. This should show the Oracle path being used by the system. Go to Oracle Bin Directory (...\product\11.1.0\client_1\BIN)
Look for a DLL beginning with ORAOLE*.dll (Oracle 11: OraOLEDB11.DLL Oracle 10: OraOLEDB10.DLL, Oracle 9: OraOLEDB.DLL)
Oracle Thin Client does not install ODAC for Oracle connections and it does not install OLE dlls.
Oracle Runtime does install the ODAC but the OLE dlls are a custom option.
Oracle ADMIN does install ODAC, OLE dlls and all language packs, which is the preferred option.
- Manually register OLEOracle 10 DLL – Oracle 10 does not always add the OLE DLL to the registry. Open up the DOS command prompt and type PATH. This should show the Oracle path being used by the system. Change directory to the Oracle bin (...\product\10.0.2.0\client_1\BIN)
In the DOS command prompt(run as administrator): regsvr32 OraOLEDB10.DLL
- How does Oracle handle UNICODE characters:
Oracle does not have NVARCHAR or NCHAR like SQL SERVER. It uses VARCHAR for Unicode and non-Unicode data. It will add more characters to support the DOUBLE BYTE data. If you see a field defined as VARCHAR(9) instead of the normal VARCHAR(3), it will have been a 3 times increase in size to support Unicode characters. There is no coding change to the APP or the driver but users do not like to see the data elements 3 times larger than normal. If the SAP settings on the vertical view of Target Source are entered, then the Build Package should only download the correct column width using DD03L for it settings. - Assemble / CranPort can have access issues on custom data types.
A client created some custom data types in Oracle and tried to download via Collect. There was a permissions error when running the CranPort package. Change the Data Type conversions to support loading this Custom Type as NVARCHAR(500). CranPort will now work because it did not have to create the table.
Tips to working with Oracle Financials
- Oracle Financials is not like SAP because it has 238 Schema Owners in the system.
Examples: AP (Accounts payable), APPS (View for all apps), AR (accounts receivable),
PO (Purchase Orders), INV (inventory)…etc. - It is important to FETCH Tables to get all schema owners. Verify the download SQL command does not have a filter on the SOURCEDBO.
Example with Source DBO Filter:
SELECT OWNER AS SchemaOWNER, TABLE_NAME "TABLENAME", 'Download Table' "Description"
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER = '#SOURCEDBO#' AND OWNER <> 'SYSTEM' AND OWNER <> 'SYS'
ORDER BY 1 ASC, 2 ASC
Example without Source DBO Filter:
SELECT OWNER AS SchemaOWNER, TABLE_NAME "TABLENAME", 'Download Table' "Description"
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER <> 'SYSTEM' AND OWNER <> 'SYS'
ORDER BY 1 ASC, 2 ASC - It is important to set the PUBLISH Schema when importing Groups into Target Source of Collect. This is the biggest mistake of the user dealing with Oracle Financials.
This will put the correct schema of the Table or View at the table level in Collect.
Tips to fixing DB2 Connections
- All DB2 connections must be ODBC or OLE. For ODBC, go to this exe to see the drivers and the System DSN. C:\Windows\SysWOW64\odbcad32.exe
- DB2 Connection strings: Always enter the SERVERADDRESS(Hostname), Database (DO5),
User ID(DB2READ) and Password(Ronly!)
OLE DB2 Example:
Provider=IBMDADB2;Database=DO5;Hostname=223.90.55.108; UID= DB2READ; PWD=Ronly!; PORT=5915;Protocol=TCPIP;
ODBCDB2: DSN=DSN_D05; UID=DB2READ;PWD=Ronly!;
ODBCDB2AS400: DSN=DSN_D05; System=223.90.55.108; UID= DB2READ; PWD=Ronly!;
For more complete examples of connection string for a driver go to this URL(http://www.connectionstrings.com/) - For AS400, If the database is set to SPANISH and a column collation is set to Portuguese, then ISeries driver will not work. You need to use Hit Software OLE DB2 driver or IBMDADB2.dll
- For AS400 ISeries driver, If the database is set to another language, a user can try using the CODEPAGE=(to the language number) in the connection string to convert the Unicode characters.
See Web site for list of code pages:
http://msdn.microsoft.com/en-us/goglobal/bb964653.aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/dd317756(v=vs.85).aspx
http://en.wikipedia.org/wiki/Code_page
- Verify Un-committed read is set in ODBC setup exe, this can save DEADLOCKS and TIMEOUT error. The setting will be in different places depending on the Driver version.
https://www-304.ibm.com/support/docview.wss?uid=nas8N1017566
- For OLEDB2 to fix Unicode issues, Check to see if the IBMDBDB2.dll driver is installed.
Go to command prompt and type PATH. Find the path for the IBM folder and drill down into the BIN directory and see if dll exists.
Tips to working with DBMoto
- DBMoto must be installed on the APP server with DSP. This is done by default on the DSP 6.1.
- DBMoto Services: Data Replicator Service must be running to download data from Source to Target. Data Replicator Service must be stopped when building package from Collect for either DBMoto Refresh or DBMoto Mirror. Server Agent must be running for Collect to talk to DBMoto or for the DBMoto Management Console to work on the App Server.
- Is DBMoto_Client under local server set as the metadata library?
DBMoto will point to a local file metadata repository by default on install.
It must be deleted and added back as DBMoto_Client pointing to the same SQL SERVER Instance as DSP. The database DBMoto_Client is already created as part of the install of DSP.
- Is the Global Script Installed? Go to DBMoto Management Center, Click DBMoto, Click Local and right click on DBMoto_Client and select Global Script. The CLIENT should be update to the Target Value of the instance.
The contents of the Global Script should look like this:
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Namespace DBRS
Public Class GlobalScript : Inherits IGlobalScript
PUBLIC SHARED CLIENT as String= "120"
Public Shared Function RemoveLeadingZero(obj as Object) as Object
Dim ZERO() As Char= {"0"}
if obj is nothing then
return obj
end if
Dim Str as string = obj.ToString().TrimStart( ZERO )
IF Str.length() = 0 Then
Str = "0"
End If
return Str
End Function
End Class
Public Class MappingRule : Inherits IMappingRule
End Class
Public Class GlobalEvents : Inherits IGlobalEvents
End Class
End Namespace - Does the Target connection name match the name in Collect. (DBMoto is case sensitive and must match Collect by case). If dgSAP is entered in Collect, then the Target needs to be entered as dgSAP not DGSAP or DgSap.
- Does the Source connection name match the name in Collect. (DBMoto is case sensitive and must match Collect by CASE). DBMoto does not support OLEOracle connections. Collect can have OLEOracle but it must be configured using Oracle inside of DBMoto. If Source is entered as Ebs_Ap then it needs to be entered as Ebs_Ap in DBMoto management center.
- Remove leading zeros can be done automatically by DBMoto via VB Scripts
Go to Collect-> Administrative -> Advance Options to activate/de-activate or add new entries to the list of tables and columns combinations.
- Parameter Collect configuration needs to be adjusted depending on the security settings on the App server. If the Build Package regularly times out, it means the Start Replicator cannot be stopped via IIS. A user must log on to the server and manually stop the DBMoto Data Replicator Service. If a user is building a lot of packages, it would be faster if the Start Replicator and Enable Groups were turned off until all the builds are done for the Target. 300 seconds is the default for mirroring tables but it can be changed. This time is set inside of the Replication Packages so rebuilds of all replications must be done to pick up any changes. If the Server Address does not match the install site or DBMoto Server Agent is not running, a user will get cannot connect to server error.
- Build Package fails for Timeout or Security
If the Build Packages fails with a Timeout Error, the IIS process does not have access to stop the DBMoto Replicator. DBMoto Mirror is a change data capture process that does an initial full download and then applies inserts/updates/deletes to the table as they occur on the source DBMS. It will run post RULE processing a few minutes after the download for NON-Key fields.
DBMoto Mirror will normally put tables in groups to support Transaction level updates. This means all material tables are updated at the same time.
DBMoto Download is a single table download not related to any other process.
- Source DBMS settings – requires extra security to make the mirroring process work correctly.
This user access is documented in the Hit Software install guide and a user will have errors if using the normal READ ONLY access for Collect.
Here are a few brief highlights for the top 5 DBMS types:
Oracle – Ability to add supplemental logs at table level and Oracle Log miner must be installed.
Grants to a number of system tables also required. Please refer to DBMoto install guide for exact instructions.
DB2 Mainframe – Change data capture works with table triggers to capture changes.
Please refer to DBMoto install guide for exact instructions.
DB2 AS400 – Change data capture requires a HIT Software component to be installed. Please refer to DBMoto install guide for exact instructions.
DB2 AIX – Change data capture requires admin rights to turn CDC on and off per table. Please refer to DBMoto install guide for exact instructions.
SQL SERVER – Transaction Replication must be enabled and User ID must have Sysadmin access to create and remove Transaction logs. Please refer to DBMoto install guide for exact instructions.
Tips to working with Data Services
- Data Services connection are 64-bit (NOT 32-BIT). The correct 64-bit drivers must be installed.
- All Data Services ODBC connections must be configured ODBC using this executable (C:\Windows\System32\odbcad32.exe) on the Data Services Server.
The Target and Source names must match between Collect and Data Services. - Data Services should not be installed on the APP server of DSP
- Data Services does not support OLE connection types
- Data Services does not connect to all version of DB2. It is designed for WINDOWS, AIX and LINUX also known as DB2UDB.
- Data Services has connection for SQL SERVER and ORACLE but all other connections are made via ODBC connection strings.
- List of DSP Connection that can be used by DSP:
ODBCDB2UDB – Yes
ODBCDB2 – MAYBE (Does not support IBM Mainframe DB2 or AS400, please use the UDB Driver)
ODBCOracle – Yes, Oracle 7, 8, 9 and 10 were not certified.
ODBCInformix – Yes
ODBCSybase – Yes, Only Sybase ASE version
SAPAPPSERVER– Yes and will work with SAP RFC or BOA RFC downloads
Oracle – Yes for any version of Oracle that the 64-bit can read against.
Oracle 7, 8, 9 and 10 were not certified.
SQL SERVER – Yes for any version of SQL SERVER (2000 or higher).
NOTE: SQL SERVER 2000 or 2005 was not certified but it should work.
ODBCAttunity – NO (Data Services Yes but DSP DLL does not have the Source)
ODBCDB2AS400 – NO
ODBCDB2HIT – NO
ODBCIMS – NO
ODBCMAXDB – NO (Data Services Yes but DSP DLL does not have the Source)
ODBCProgress – NO
ODBCTeradata – NO
Data Services DOES NOT SUPPORT ANY VERSION of OLE for any product.
Tips to fixing Informix Connections
- All Informix connections must be ODBC. For ODBC, go to this exe to see the drivers and the System DSN.
C:\Windows\SysWOW64\odbcad32.exe - Verify IUSER had READ and EXECUTE on the Informix Folder has read and execute permission to the Informix Folder. Push permissions down to all sub-folders and files.
- Schema Owner and Table Names are always entered in lower case!
The exception to the rule is RFC downloads will require table to be entered in UPPER CASE.
T001S using BOA RFC or SAP RFC will require Upper case. RFC do not use the schema owner so do not worry about that setting. mara (all lower case) is a transparent table and must be entered in lower case.