How to access to non Lawson system DB tables in Design Studio v.9

You can added them in using DBDEF. You will probably want to setup a view data area so it doesn't try to create the tables. You can also add access to SQL views in with DBDef. The one issue we have is that you need to bounce the server after doing dbdef changes. If any of the tables have varchar data in them you need to run a 'edita'(sp?) tool and tell Lawson that the table or view contains varchar data.

I think Jay's suggestion is the easiest way. You can also add a custom system code such as ZZ and add your new tables to the custom system code just to keep them separated. You should assign the table or the entire system code to a view dbspace if they are in fact views; or not if you want to create a table. After this is done, you can use DS or any other standard Lawson tools to access the data.

Thanks Ben & Jay for your helpful Info.

Thanks

 

LID in:

DBDEF > choose a productline > F6 > Files > pick one of the files to look up how it's setup. (they are actually TABLE structure definitions)

now create a new table via DBDEF

run "blddbdict  productline "

"dbreorg -lc productline " to see the changes will be made in report mode.

"dbreorg productline " to actually create the table.

Now you can use DS against the newly created table.

Keep in mind that these steps will create an actual table in the Lawson database when you run the dbreorg.

If you are trying to access already-existing non-Lawson data, you need to add a couple more steps, such as adding the table in its own system code and/or assigning it to a new "database space" that is defined as a view.

Finally, depending on what you're trying to do, your "Lawson" database has to have access to the other database (i.e. you can't attach to multiple MSSQL databases from the same product line).

Thank you very much John & Jimmy. But I need more help on this. When I insert new system code and try to asign an existing non lawson table (which is in the same Oracle table space but different schema) to it but I couldn't see it from the the list. Here what I did.
- run dbdef
- select Product Line
- Insert new System Code
- F6 to define it and select 'A' to define a file
However I don't see any non-lawson db file in there. Did I miss something?

Thanks

If it was only that easy. You will need to add in the table name and each and every column by hand.

I am fairly sure the table needs to be in the same schema as Lawson is. You may create a SQL view on top of the table in the Lawson schema if you do not want to move the table to the Lawson schema.

If the table already exists you need to setup the table in a view space otherwise Lawson will attempt to create the table and then fail.

Thanks Jay;
Yeah! I have about 50+ tables not in Lawson Schema that I'm trying to buid on. so I don't want to move them over to lawson schema. If I create a view on the table How do I setup in dbdef to hook it up to a new system code.? When I try to assign a file (table) to a system code I see only lawson files.

Appreciated for your help.

What I would do is, before creating a view, go into dbdef, go to the new system code, and add a new table with the same name as your existing table in the other schema. Add each column with its attributes one at a time. When you save the definition and get out, then run blddbdict & dbreorg and a new, empty table will be created in the lawson schema with the requested columns. Now, carefully compare the attributes of the empty table with the real table to make sure the column attributes match. (If not, make changes in dbdef and repeat the blddbdict & dbreorg).

When the two tables appear to be in sync, then, with Oracle tools drop the empty table in the lawson schema, and create a view in the lawson schema based on the real table, with the same columns as the empty table you just removed.

Then as an optional but recommended step, change the dbspace assignment for the new table in dbdef to a view dbspace.

There are other ways to do this. In particular you can add the view directly without creating the empty table first. But doing this way allows you to do the compare and verify that all the attributes are set up correctly and could prevent less obvious problems. This should work fairly easily, other than the tedium of entering all of the columns by hand.

DBDEF will _not_ display a list of non-Lawson tables or views. You must enter in the table name that you wish to tell Lawson about. Keep in mind that Lawson refers to tables as files. I would start by getting just one simple view or table working and then move on.

To setup a new view or table in Lawson:
1. Go into DBDEF
2. Select your product lne
3. Press F6 to bring up Define
4. Select A. Files
5. Enter in the name of the file(table) that you wish to create.
6. Choose a system and prefix.
7. Enter in each column that you want to be visible in Lawson. Sorry DBDEF will _not_ automatically pull in the column names for you.
...

To setup a view space:
1. Go into DBDEF
2. Select your product line
3. Press F6 to bring up Define
4. Select E. Database Space
5. ....

To setup a table/view as a view space:
1. Go into DBDEF
2. Select your product line
3. Press F6 to bring up Define
4. Select C. Data Areas/Data IDs
5. Press F6
6. Select B. File Size And Database Space
7. Find your custom file and change the database space to the view space.

Thanks for your suggestion Ben. I will give it a try and let you know how it goes.

Thanks again.

Thank you very much Jay. Your helps save my time a lots.

Thanks

After adding new system code (ZZ) and setup a new table file(TESTFIELE) in that system code, run blddbdict and dbreorg. Now I can see the new table in DS but I get this error when ran it:

at com.lawson.ios.dig.db.DigObject.getDbFile(DigObject.java:664)
at com.lawson.ios.dig.db.DigObject.executeInternalRequest(DigObject.java:218)
at com.lawson.ios.dig.db.DigObject.executeRequest(DigObject.java:164)
at com.lawson.ios.dig.db.Dme.executeRequest(Dme.java:72)
at com.lawson.ios.dig.ErpDmeAgent.processRequest(ErpDmeAgent.java:111)
at com.lawson.ios.agent.container.AgentContainerImpl$AgentWrapper.processRequest(AgentContainerImpl.java:467)
at com.lawson.ios.dig.DmeBroker.processRequest(DmeBroker.java:59)
at com.lawson.ios.agent.container.AgentContainerImpl$AgentWrapper.processRequest(AgentContainerImpl.java:467)
at com.lawson.ios.agent.container.AgentContainerImpl.processRequest(AgentContainerImpl.java:239)
at com.lawson.ios.servlet.Router.doGet(Router.java:208)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1068)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1009)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:145)
at com.lawson.servlet.TransformFilter.doFilter(TransformFilter.java:126)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:87)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:771)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:679)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:539)
at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:744)
at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1455)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:115)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:454)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:383)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102)
at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:195)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:743)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:873)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1473)
]]>

Any ideas ?

Thanks

You may try using say the query wizard on the table and seeing if that gets you a better error message. After doing the blddbdict and reorg have you bounced the system? What is in ladb.log?

Jay;

I use sqlplus to query from the new table but look like it wasn't created in oracleDB yet ( table does not exist) so Why I can see it in DS. There's no logged record in ladb.log for this event. The error I got is:

File TESTFILE is not valid for data area PROD_1

Thanks

If you created the table in a view table space then dbdef will not create the table for you. The reason you can see the table in DBDEF is because that is only a record of what Lawson thinks is the database not what is actually there. There could be some other issue causing the problem although I think the dbreorg would fail if the dbreorg tried to create a table and couldn't. It does fail on SQL Server if it tries to create a table and cannot. At this point you will probably just end up creating the table by hand.

Technical Debt

I created a new table from scratch and run blddbdict but there's an error; it processed index and element fine but failed on condition when processing WODETAIL file:
Condition Field PRINT-STRING : Condition SERIAL Not Found.

I look at WODETAIL file condtion and SERIAL was defined there.
How do I correct this error and rebuild dict.

Thanks a lot for your help