Using 2 Different DB's for JDE

Using 2 Different DB\'s for JDE

We are currently using JDE 8.12 with DB2 on an AS400.
We are implementing BI on a SQL server 2008 R2. I am trying to copy data via OMW/UBE to this SQL server from the AS400 unsuccessfully. Here is what I have done:
- Created a datasource for the SQL server/DB for the system
- Created an ODBC with the latest SQL ODBC Driver, was able to connect to this server via test
- On SQL server I have created a duplicate DB id that we are using on the AS400
- DB ID has Sysadmin rights on SQL, I was able to login to SQL manager and DB and create a table.
- P9654A Data server – SQL server added not via plan but added
Here is the error I am getting:

4080/1280 WRK:Starting jdeCallObject Tue Nov 15 15:39:40.925001 Jdbodbc.c1344
ODB0000163 - SQLDriverConnect failure. rc=-1

4080/1280 WRK:Starting jdeCallObject Tue Nov 15 15:39:40.972001 Jdbodbc.c1344
ODB0000164 - DBC:00 [28000][18456][1] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'SYS_ID'.

4080/1280 WRK:Starting jdeCallObject Tue Nov 15 15:39:40.972003 Jdbodbc.c1344
ODB0000164 - DBC:01 [28000][18456][1] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'SYS_ID'.

4080/1280 WRK:Starting jdeCallObject Tue Nov 15 15:39:40.972005 Jdbodbc.c1354
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DB_NAME.

4080/1280 WRK:Starting jdeCallObject Tue Nov 15 15:39:40.972007 Jdb_drvm.c921
JDB9900164 - Failed to connect to DB_NAME


Debug- ERROR INFO JDEERR - ID= 2, Error= 4838 - \Deployment\E812\DV812\package\DV812FB\source\B9800200.c, Line= 143

ODBC[Jdbodbc.c,4023] wSQLExecute failure. rc = -1
Nov 16 09:23:50.446001 - 3504/3968 WRK:Starting jdeCallObject ODBC[Jdbodbc.c,4023] STMT:00 [42000][2760] [Microsoft][SQL Native Client][SQL Server]The specified schema name "SYS_ID" either does not exist or you do not have permission to use it.
Nov 16 09:23:50.446002 - 3504/3968 WRK:Starting jdeCallObject ODB0000163 - wSQLExecute failure. rc=-1
Nov 16 09:23:50.446004 - 3504/3968 WRK:Starting jdeCallObject ODB0000164 - STMT:00 [42000][2760][2] [Microsoft][SQL Native Client][SQL Server]The specified schema name "SYS_ID" either does not exist or you do not have permission to use it.

I cannot find the install docs but are there any scripts I need to run to grant access?

Thanks in advance!

Re: Using 2 Different DB\'s for JDE

HI,
just curious, why don't use an ETL tool for data extraction?

Re: Using 2 Different DB\'s for JDE

From the BI side, I wanted to see if I can do it through JDE first. They are working on the ETL side too.

Re: Using 2 Different DB\'s for JDE

The AS400 cannot natively talk to a SQL server so the UBE running on the AS400 will not be able to connect to a SQL server. If you really wanted to use a JDE to do this you would have to setup a Windows based enterprise server and run the UBE there so it could connect to your database on the AS400 via ODBC. You could also run the UBE locally on a fat client.

You could also probably write a custom program outside of JDE that would extract the data much more efficiently than JDE. But as other posters have said an ETL tool should be the direction to go.

Re: Using 2 Different DB\'s for JDE

I was planning on running the UBE local on a pc. This is more of a side project . My issue is the connecting to the SQL DB and the errors I am getting with permissions/schema. Is there a script I need to run on the SQL server to grant JDE proper rights? All I did was made them a sysadmin for that DB.