HELP - resident SQL/Web Developer stuck inside of AS400

[tman24m]

Can anyone help me?

I've been working with an IS department for the last couple weeks who really knows nothing more than life inside of the 400. Everytime I ask for something, we always come up against a roadblock because the 400 can't do it or they just don't know how.

I've been working on developing web applications for the 400. So far, I've been told that with the AS400 you can't do a view on top of a view, can't join more than two tables in one view, and can't do a full outer join. Being a SQL programmer, I don't really understand these limitations. I don't want to replicate the data if I don't have to. How much of what I'm being told is true? Any suggestions?

Thanks
Totally Frustrated

 

[blom0344]

Hello Tman24m,

When I saw your tread, I turned around to the AS400 programmers and posed the questions you came up with (as far as I did not know them myselve) A view in AS400 is a 'logical', is primarily used for getting data from a table in a different order or just to show active records. It rarely if ever done over more than 1 table (physical in AS400 terms), let alone over 2 tables. Should be possible though. When mentioning full outer joins, collegues started to look at me in a funny way and started to reference SQL. Clearly AS400 regards SQL as not native, everything more complex than a simple logical is dealt with by writing a new program. I'm used to views , so I think your amazement is understandable. Mentioning views over views (in this case logicals over logicals) really had them dropping their jaws. It is not possible and why would I want to do that!!!?
Clearly these AS400 guys are a special flock of birds.

I use DB2 UDB for every kind of purpose , making sure to replicate AS400 data first to UDB. Everything is possible there! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Rich96]

Tman24m,

Hmmm, sounds like your '400' dude is a little on the blind side! You can do just about ANYTHING with a DB2 database. As well as interactive SQL, Batch SQL, COBOL, RPG, CL, C, Java, HTML, XML, ....... all on your iSeries box. It sounds like a lack (very lack) of knowledge and maybe a little of the lazies.

You can create a view on a view (logical on a logical) but I'm with blom0344 why would you?? You can join as many tables as you want as well. My suggestion would be to hit the websites and absorb all the knowledge you need to accomplish your task because apparently you not going to get any help.

Rich

 

[leveyp]

Agree whole heartedly with Rich96
DB2/400 will do everything you ask. Try downloading a copy of DB2 Universal Database for iSeries SQL Reference

from IBM Support

http://www7b.software.ibm.com/dmdd/library/techarticle/0206sqlref/0206sqlref.html