AS400 and SQL Views

[JMR]

Ok all you 400 gurus: I am trying to do the SQL view thing on an as400. I've done this many times with SQL Server, but this is my first attempt on the AS400.

What I've done so far:

1. Created a view using CREATE VIEW in interactive SQL. This created my object on the as400 (TESTDTA/F55TEST...a logical file).
2. Changed the authority for *PUBLIC to have *ALL authority...it auto changed to 'USER DEF' which is how other JDE logical files are defined.
3. Defined the table in JDE so it matches the definition on the 400 (did not generate table)
4. Created a BSVW

Behavior:

- On the as400, if I select * from testdta/f55test I get the results I expect
- In UTB, I can see the records in the table IF I uncheck the 'Format data' checkbox. Otherwise the window for the table does not even open.
- I created a quick test app (find browse using my new bsvw) and when I try to run the app, I get the following message:

jdecgrt: Unable to retrieve Business View information - application P55JRTST

Why does UTB not want to work when the 'Format data' checkbox is checked?
Am I right in using the CREATE VIEW command in SQL?

BTW: This is an Xe implementation

Thanks!

 

[Bill Dotson]

You don't mention -- did you build and deploy at least an update package containing this view, for both the client and the server?

 

[DBohner-(db)]

There are many tricks to SQL view on the iSeries (or whatever they are calling it these days).

Make sure that all your JDE Filespec fields match those in the Iseries View (same type of fields, lengths and types)

Make sure that you have reset the security of the view - to allow JDE's pass-thru account(s) to access it

Look in your JDE.LOG - it is your friend (it really is). If there are any alignment or column-type issues, your JDE.LOG will be able to identify it for you!

(db)

 

[JMR]

Bill,

No I did not put either the TBLE or BSVW into an update package. I shouldn't have to as I am only in the testing stage and the specs for both reside on my client.

 

[JMR]

Daniel,

You forced me in the right direction. I went to the 400 to do a dspffd and as soon as I saw the first field, I realized my mistake. My view is a join from ITM to an 8-char field. In my view I named the 8-char field xxITM, so my view's ITM was defined differently than JDE (8 digit numeric field).

Thanks, dude.

 

[GSI Inc.]

1. You need to generate the table in JDE
2. I'm not sure how you created the BSVW without a gen of the table?
3. You will continue to get odd results in the BSVS, UBE and anything that uses the table on the as/400 until you gen it.

When you run the UBE are you running it locally? I assume you are

Angelis

 

[JMR]

Hi Angelis,

1. Sorry for the confusion, but the subject of the post is about creating a database view (in the native database) that you can define in JDE as table. In this case, you do NOT generate the table. The only thing you need to do in JDE is create the specs for the table in TDA but never generate it.

2. A BSVW only needs table specs to be created. It does not need the table to be generated.

My testing was using an APPL, not a UBE so, yes, it was running locally.

Anyway, all is fine now if you read my last post. Thanks.

 

[DBohner-(db)]

Angelis,

A SQL View is a 'virtual' table built over an SQL Statement.

"Create View F55666 as select * from F0911 where date > something"

The virtual table F55666 would be created with all fields from the F0911 and all records greater than a specified date.

In thes case - you could copy the F0911 specs in JDE to F55666 - and view/use the data from the F0911 as table F55666. You WOULD NOT generate table F55666 - you would be able to immediately see all the rows with a date > something... F55666 becomes a virtual subset of the data in the F0911.

This process is call Virtualizing Tables - via SQL/Database Views.

Maybe this would be a good subject for me to cover in Collaborate '07?

(db)

 

[Bill Dotson]

Daniel,

Good idea! I've often suspected that there might be substantial benefit to using SQL views in JDE as opposed to the limited JOIN capabilities of JDE. The limit to the number of JOINs in a SQL view is much greater than JDE, and there may be performance benefits as well.

Sounds like a good session. Can you hold it at a Blackjack table?

 

[MagarG]

[ QUOTE ]
Daniel,

Good idea! I've often suspected that there might be substantial benefit to using SQL views in JDE as opposed to the limited JOIN capabilities of JDE. The limit to the number of JOINs in a SQL view is much greater than JDE, and there may be performance benefits as well.

Sounds like a good session. Can you hold it at a Blackjack table?

[/ QUOTE ]

Ah, exactly what we did when we needed to join two fields that were not the same data type in EnterpriseOne but the character field held the work order number. We used the digits function on the AS400 to accomplish the task.

...
...
SELECT F0618.YTAN8, F0618.YTPALF, F4801.WAWR02, F0618.YTSBL, F4801.WADL01, F0618.YTDWK, F4801.WASTRX, F4801.WAWR01, F0618.YTPHRW, F0618.YTRCPY, F4801.WASRST FROM DV811DTA.F0618 F0618 INNER JOIN DV811DTA.F4801 F4801 ON F0618.YTSBL = DIGITS(F4801.WADOCO) ;
...
...

 

[JEMILLER]

I use the digits technique a fair bit with World to E1 converted sites. It gives the World soft-join feature to E1 by allowing the joining of numerics to characters.

I also use joins for aggregates (It is sad that the E1 view design tool doesn't give us basics like SUM, AVG, COUNT!). You can achieve FASTR-like performance for E1 FRW reports by having the DB engine do the aggregation in a view instead of making the UBE engine read in all the records and aggregate them. It takes a bit of extra development work but the performance benefits can be well worth it.

 

[jolly]

The performance benefits in this trick can be incredible when you are using SQL aggregate functions such as sum().