Business Objects on AS/400?

general discussion
1

Hello,

Does anyone know if Business Objects can run on an AS/400 platform? If so, what are the particulars and any issues?

Happy Holidays!

Julie

Listserv Archives (BOB member since 2002-06-25)

2

Hello,

Does anyone know if Business Objects can run on an AS/400 platform? If so,
what are the particulars and any issues?

Happy Holidays!

Julie

Listserv Archives (BOB member since 2002-06-25)

3

BO can certainly be used with the AS/400 - even installation and management of BO Repositories have been successfully tested. Unfortunately, AS/400 SQL imposes several important limitations, depending on which OS version you’re running. Here are a few problems I’ve run into over the last couple of months:

  • ‘right outer join’ not supported! means that you’ll have to (re)arrange your tables or (re)define your outer joins so that they’re all of the ‘left outer’ type… can be hard when 2 or more tables need to be outer joined in succession.

  • functions are not allowed in the GROUP BY clause: in other words, make sure to have any calculated/derived data stored as such in your tables or you’ll have to manually strip all functions from objects in GROUP BY, hence affecting the way measures are aggregated.

  • lack of conditional functions: functions like Oracle’s DECODE were totally absent until V4R2 (CASE)

  • performance is extremely hard to tune: in the more recent OS/400 versions (as of V4R2), use of existing indexes seems to have improved a lot, although there are still a few things I would like to test in this regard. If you’re running an older OS version, better make sure you have good logical files for optimising your joins. (make sure the indexed fields appear in the correct order in your logical key!) Other logicals can then be used in pre-defined conditions. one thing is clear: AS SOON AS THE MACHINE DECIDES THAT AN INDEX NEEDS TO BE CREATED FOR A PARTICULAR QUERY TO BE RUN, UP TO 80% OF THE MACHINE’S CPU CAPACITY RISKS TO BE ALLOCATED TO THE SQL JOB. Imagine what this implies at multi-user level.

I recently spent quite a lot of time trying to figure out if OS/400’s Query Optimizer is also activated by ODBC calls - since this now appears to be the case, I suggest you take a look at
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/BOOKS/QB3AUC02/CCONTENTS and http://iws.as400.ibm.com/db2/db2main.htm, and more specifically at the sections regarding index usage and Query Optimizing as these will be the main issues to worry about in terms of performance.

good luck, and feel free to get in touch with me direct for any additional questions.

mirko.

Listserv Archives (BOB member since 2002-06-25)

4

BO can certainly be used with the AS/400 - even installation and management
of BO Repositories have been successfully tested. Unfortunately, AS/400
SQL imposes several important limitations, depending on which OS version
you’re running. Here are a few problems I’ve run into over the last
couple of months:

  • ‘right outer join’ not supported! means that you’ll have to (re)arrange
    your tables or (re)define your outer joins so that they’re all of the ‘left
    outer’ type… can be hard when 2 or more tables need to be outer joined
    in succession.

  • functions are not allowed in the GROUP BY clause: in other words, make
    sure to have any calculated/derived data stored as such in your tables or
    you’ll have to manually strip all functions from objects in GROUP BY, hence
    affecting the way measures are aggregated.

  • lack of conditional functions: functions like Oracle’s DECODE were
    totally absent until V4R2 (CASE)

  • performance is extremely hard to tune: in the more recent OS/400
    versions (as of V4R2), use of existing indexes seems to have improved a
    lot, although there are still a few things I would like to test in this
    regard. If you’re running an older OS version, better make sure you have
    good logical files for optimising your joins. (make sure the indexed
    fields appear in the correct order in your logical key!) Other logicals
    can then be used in pre-defined conditions. one thing is clear: AS SOON
    AS THE MACHINE DECIDES THAT AN INDEX NEEDS TO BE CREATED FOR A PARTICULAR
    QUERY TO BE RUN, UP TO 80% OF THE MACHINE’S CPU CAPACITY RISKS TO BE
    ALLOCATED TO THE SQL JOB. Imagine what this implies at multi-user level.

I recently spent quite a lot of time trying to figure out if OS/400’s Query
Optimizer is also activated by ODBC calls - since this now appears to be
the case, I suggest you take a look at
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/BOOKS/QB3AUC02/CCONTENTS
and http://iws.as400.ibm.com/db2/db2main.htm, and more specifically at the
sections regarding index usage and Query Optimizing as these will be the
main issues to worry about in terms of performance.

good luck, and feel free to get in touch with me direct for any additional
questions.

mirko.

Listserv Archives (BOB member since 2002-06-25)