Tuesday, May 5, 2009

Exploring iSeries QSHELL: (Innovative Idea for People want to develop application on AS400)

I am sure after reading this article most of the People will be surprised to see that there is something called as Shell programming in AS400 , Yes .. !! Its true .. Refer to the article below ..

If over the last few months or years, you thought of acquiring UNIX - or Linux - skills but never had the time, the opportunity or a platform to use it, this article may for you. The iSeries operating system now offers a built-in UNIX environment. This new environment is named QSHELL.

Why QSHELL? The first question one may want to ask is why did IBM come up with this 'QSHELL' environment in the first place? The answer is that to be fully Java compliant, IBM needed to supply a JDK (Java Development Kit) on its iSeries eServer that could run standard Java commands such as Java, Javac or Javadoc, the same way other (UNIX) systems could.

There is only one snag, OS/400 does not happen to be 'a flavor of' UNIX. The OS/400 IBM team resolved this hurdle by supplying a new 'UNIX style shell' on the iSeries and appropriately named it 'QSHELL'.

The purpose of this article is to give a quick overview of how QSHELL works, and provide a strong tutorial base and an example of shell script which can be run on a UNIX platform as well as your own iSeries using QSHELL.

Jumping In

To start QSHELL, simply type the OS/400 command 'STRQSH' on the command line. Once there, what you will see is not very different from an ordinary green-screen QCMD session, with a different set of commands.

At first glance

The first thing you will see after entering the QSHELL environment is a screen saying
'QSH Command Entry' , and a '$' character
To know what you can do with QSHELL, there is no 'HELP' command, and the F4 key is plainly ineffective. If you wish to know what tools you have, you need to get yourself to the /usr/bin directory. To do this, you have to type the command 'change directory to /usr/bin' – the actual command below is
cd /usr/bin
From there, you can confirm where you are by typing a 'Print Working Directory' – the actual command below ispwd
Once there, you can see the contents of the /usr/bin directory by using the command ls
or as I prefer to use it,
ls –ltra
in this case, the command modifiers
'a' means 'all', do not hide the directory entries starting with '.'
'l' means 'long', show all the details about each entry
't' means sort by modification time
'r' means reverse order for the sort. Effectively, the latest entries will be the last ones in the listing, which is practical for reading.
Note that the order of these command modifiers is not important. ls –artl is interpreted same as ls –ltra
You can also type, from the original directory the command
ls –ltra /usr/bin
This sounds basic, but ls and pwd are some of the most useful commands. You will use them often in this environment.
The directory structure, in UNIX, starts with '/'. The '/' directory is also known as the 'root' directory. Each directory branch grows from the previous, just like in DOS or in Windows. The directory structure in this environment can have n levels.
The equivalent of the '/' root directory in UNIX in the traditional iSeries environment is the QSYS library. However, on the iSeries, we have a single level of libraries: all libraries are in QSYS, no library can be in a library if it is not QSYS.
In UNIX, a directory can be in a non-root directory.
One other important feature of the UNIX file system: unlike the iSeries or NT, it is case-sensitive. This means that a file named caseSENSTIVE is not the same as a file named CASESensitive. They will be listed and recognized by the operating system as two distinct files. This is critical if you write shell scripts
mkdir /mysandbox

This action of creating a QSHELL directory is similar to creating your own library. Within that directory, you can store your own shell scripts without cluttering the root area, which you want to keep clean if possible

There are many UNIX commands to play with, and for now, I have given focus on the method of using QSHELL rather than explaining too many commands... Watch out for my other articles soon regarding this , till then folks keep trying using QSHELL in ur System .....

Thursday, April 30, 2009

How to open Notepad from As400 Server Command line( StressBuster )

Hey its seems to be exciting but its true .. we can execute any PC Command that we can use in our Dos in AS400 now .

like calc.exe we use to open calculator

notepad.exe to open Notepad and many more

To do so ,

The first step is to

Start iSeries Access Organizer by using command

(STRPCO)

The Start iSeries Access Organizer (STRPCO) command starts the
iSeries Access Organizer on the host system.

Simply fire STRPCO Press F4 the put *yes in Text Assist

The second step is to

Start PC Command by using command

(STRPCCMD)

example

STRPCCMD PCCMD(NOTEPAD.EXE)


and u will see a notepad in front of u ... Is'nt it that surprising..

Just give a try and it will be great fun especially when u get tired after lots of coding .. lolzzzzzzzz

Monday, April 6, 2009

AS400 SQL - Update a field from another file


Updating a field from another file on the AS/400 is done differently than most SQL-based systems. This example uses a JD Edwards table. Here's how to do it:
Syntax:
UPDATE Library/File1 Set Field1= (SELECT Field2 FROM Library/File2 where File1.Id1 = File2.Id2) WHERE File1.Id1 IN (Select Id2 FROM Library/File2)

IMPORTANT!!!: If you leave off the second WHERE statement, you'll wipe out the values of Field1 where the records don't match. Be sure to test this in non-production or you might eliminate LOTS!!! of values in JDE F42119, possibly affecting your performance review if you ever get one.

Example: Update JDE Open Orders Business unit with Address Book Business Unit

UPDATE JDEDTA/F4211 SET SDMCU=(SELECT ABMCU FROM JDEDTA/F0101 WHERE F4211.SDAN8=F0101.ABAN8) WHERE SDAN8 IN (SELECT ABAN8 FROM JDEDTA/F0101)
Note: If you use ODBC in Visual Basic or another program, change the "/" to a dot. Example, UPDATE JDEDTA.F4211... Also, if field names are different, do not use qualifiers in the where clauses.

So the above would look like this in ODBC:

UPDATE JDEDTA.F4211 SET SDMCU=(SELECT ABMCU FROM JDEDTA.F0101 WHERE SDAN8=ABAN8) WHERE SDAN8 IN (SELECT ABAN8 FROM JDEDTA.F0101)

Wednesday, April 1, 2009

System i hits new low: Revenue down 92%


This morning on the radio I heard that IBM had released fourth quarter and 2008 year end results, with the company reporting net income up 12%. But, the System i didn’t fare well in the company’s announced earnings, with a jaw-dropping decrease in revenue of 92% year-on-year. Last quarter, we reported that the System i revenue was down 82%. The explanation is that the company has combined the revenues from Systems i and p in the new converged System p numbers, and no longer attributes any new Power running i sales to the legacy system. On the positive side, revenues from the converged System p server products increased 8%, the only increase for the Systems and Technology hardware segment.

It seems that IBM, a company founded on “machines” has moved on and is now finding success in software, services and financing. In fact, IBM senior vice president and chief financial officer, Mark Loughridge, told a conference call of financial analysts that the company derived 90% of it’s revenue from these segments. But Loughridge shared the company’s optimism going forward, “With the stimulus packages being put in place, the economies will start to improve as we go to the back half of the year. Likewise, for the first quarter our performance will be relatively flat, with growth especially in second half of the year.”

Tuesday, March 31, 2009

Tips: Run and call System i stored procedures

Use these tips for running and calling System i™ stored procedures.

Running a System i stored procedure

ODBC provides a standard interface for calling stored procedures. The implementation of stored procedures differs significantly across various databases.
This simple example follows the recommended approach for running a System i stored procedure.

1.)Set up a create procedure statement for the stored procedure and create it. The creation of the stored procedure only needs to be done once and it does not have to be done through ODBC. The definition that it provides is available to all ODBC as well as integrated i5/OS® applications.
2.)Prepare the stored procedure call.
3.)Bind the parameters of the procedure, indicating whether each parameter is to be used for input to the procedure, output from the procedure, or input/output.
4.)Call the stored procedure.


Example:

CREATE PROCEDURE MYLIB.MYPROC (INOUT PARM1 CHAR(10)) EXTERNAL NAME MYLIB.MYPGM LANGUAGE COBOL GENERAL

In the above example, the procedure name is MYLIB.MYPROC.

This procedure references the COBOL program MYLIB.MYPGM. The program takes one input parameter called PARM1 which is a char field of length 10. This procedure does not return a result set.

STORED PROCEDURE : Very IMP For SQL Lovers

Defination

A stored procedure is a set of SQL commands that has been compiled and stored on the database server.

Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.

Stored procedures improve performance by reducing network traffic and CPU load

Some Details

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement

CALL procedure(…)

or

EXECUTE procedure(…)

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared

Difference between Stored Procedures and Functions

a. Only function can Return a value (to be precise using the Return keyword)

b. Procedures can use ‘Return’ keyword but without any value being passed

c. Functions could be used in select statements, provided they don’t do any data manipulation inside and also should not have any OUT, IN OUT parameters

Comparison with Dynamic SQL

Because stored procedure statements are stored directly in the database, this can remove the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. However, most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements. In addition, pre-compiled SQL statements, while avoiding some overhead, add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.

Programming Tip: HIDING LINES WHILE EDITING ** Especially for those Who work in companies where Implementation is carried out Mostly.

When you are working with source members that have many source lines, it is useful to exclude certain lines from the displayed member, allowing you to see lines from different areas of the source on the screen at the same time. Place X (Exclude) on any line you want to exclude from the display. This doesn't delete the line from the source member; it simply causes the excluded line(s) to be temporarily removed from the SEU display.
You can also use the X command in block format (XX). The example below shows how to exclude lines 3 through 6 (I entered a blank after the XX command to make it more readable):

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
XX 3.00 333333333333333
0004.00 444444444444444
0005.00 555555555555555
XX 6.00 666666666666666
0007.00 777777777777777
****************** End of data *****************************

The result below shows that lines 3 through 6 have been hidden from view:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
- - - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

To redisplay the lines, press F5 (Refresh) key, or use the SF or SL commands. SF (Show first) command shows the first excluded line, and SL (Show last) shows the last excluded line. You can also use SFn or SLn to show the first n lines or the last n lines of the excluded group of lines. The example below shows how to use the SF2 command to show the first two excluded lines:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
SF2 - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

Notice below that the display shows the first two excluded lines and the text has changed to "2 data records excluded":

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
0003.00 333333333333333
0004.00 444444444444444
- - - - ------------- 2 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************