Page 1 of 1

cobal hw

PostPosted: Wed Apr 27, 2011 7:00 am
by basavasai
the hw is i have account table in which one of the column is acct_bal_cny need to be updated

You are to provide a stored procedure that will increase the balance of an account in your ACCOUNT
table based on a rating of 1, 2, or 3:
A rating of 1 will warrant a 2% increase balance.
A rating of 2 will warrant a 1% increase balance.
If not a 1 or 2, increase the balance by 0.5%.
You will use the CASE SQL procedure language construct to accomplish this. The new procedure should
return the old balance and the updated balance...


is there any error in below store produre and cobal code


CREATE PROCEDURE ZUSER02.UPDBAL (IN ACCTID CHAR(10),IN RATE SMALLINT,
OUT OLD_BAL DEC(10,2),OUT UPD_BAL DEC(10,2))
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR SQLSTATE '70444'
SELECT ACCT_BAL_CNY
INTO OLD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
CASE RATE
WHEN 1 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.02)
WHERE ACCT_ID=ACCTID;
WHEN 2 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.01)
WHERE ACCT_ID=ACCTID;
ELSE
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.005)
WHERE ACCT_ID=ACCTID;
END CASE;
SELECT ACCT_BAL_CNY
INTO UPD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
END P1


cobal code


*---------------------------------------------------------------*
* IBM SVL MINI BANKING SYSTEM *
*---------------------------------------------------------------*
*---------------------------------------------------------------*
* PROGRAM NAME : CALL A STORED PROCEDURE UPDBAL *
* PROGRAM ID : CALLSP *
* INTERFACE AREA : N/A *
* DATABASE USAGE : BINKDB *
* TABLE USAGE : ACCOUNT *
* FILE USAGE : N/A *
* FUNCTION : CALL A STORED PROCEDURE UDPBAL *
*---------------------------------------------------------------*
* PREPARED BY : JINSONG FENG *
* DATE WRITTEN : APR. 2009 *
* REVIEWED BY : JINSONG FENG *
* DATE MODIFIED : APR. 2009 *
*---------------------------------------------------------------*

*---------------------------------------------------------------*
* I D E N T I F I C A T I O N D I V I S I O N *
*---------------------------------------------------------------*
IDENTIFICATION DIVISION.
PROGRAM-ID. CALLSP.
AUTHOR. SVL.

*---------------------------------------------------------------*
* E N V I R O N M E N T D I V I S I O N *
*---------------------------------------------------------------*
ENVIRONMENT DIVISION.

*---------------------------------------------------------------*
* D A T A D I V I S I O N *
*---------------------------------------------------------------*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-ACCT-ID PIC X(10) VALUE '0000000001'.
01 WS-RATE PIC S9 COMP VALUE +2.
01 WS-OLD-BAL PIC S9(10)V9(2) USAGE COMP-3.
01 WS-NEW-BAL PIC S9(10)V9(2) USAGE COMP-3.
*---------------------------------------------------------------*
* SQL INCLUDE FOR SQLDA / SQLCA *
*---------------------------------------------------------------*
EXEC SQL INCLUDE SQLDA END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCOUNT END-EXEC.

LINKAGE SECTION.
*---------------------------------------------------------------*
* P R O C E D U R E D I V I S I O N *
*---------------------------------------------------------------*
PROCEDURE DIVISION.
MAIN SECTION.
DISPLAY "START TO CALL STORED PROCEDURE."

EXEC SQL
CALL ZUSER02.UPDBAL(:WS-ACCT-ID
,:WS-RATE
,:WS-OLD-BAL
,:WS-NEW-BAL
)
END-EXEC

DISPLAY "FOR ACCOUNT : ", WS-ACCT-ID
DISPLAY "THE OLD BALANCE IS : ", WS-OLD-BAL
DISPLAY "THE NEW BALANCE IS : ", WS-NEW-BAL
STOP RUN.

Re: cobal hw

PostPosted: Wed Apr 27, 2011 9:49 am
by NicC
What is an 'hw'? And is this a homework question or interview question? If so it should have been posted in one of those sections of the forum. And using code tags would have made the code more readable. I am not going to try and work it out because I do not have the time to align everything properly.

Re: cobal hw

PostPosted: Thu Apr 28, 2011 4:28 am
by BillyBoyo
basavasai wrote:the hw is i have account table in which one of the column is acct_bal_cny need to be updated

You are to provide a stored procedure that will increase the balance of an account in your ACCOUNT
table based on a rating of 1, 2, or 3:
A rating of 1 will warrant a 2% increase balance.
A rating of 2 will warrant a 1% increase balance.
If not a 1 or 2, increase the balance by 0.5%.
You will use the CASE SQL procedure language construct to accomplish this. The new procedure should
return the old balance and the updated balance...


is there any error in below store produre and cobal code


CREATE PROCEDURE ZUSER02.UPDBAL (IN ACCTID CHAR(10),IN RATE SMALLINT,
OUT OLD_BAL DEC(10,2),OUT UPD_BAL DEC(10,2))
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR SQLSTATE '70444'
SELECT ACCT_BAL_CNY
INTO OLD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
CASE RATE
WHEN 1 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.02)
WHERE ACCT_ID=ACCTID;
WHEN 2 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.01)
WHERE ACCT_ID=ACCTID;
ELSE
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.005)
WHERE ACCT_ID=ACCTID;
END CASE;
SELECT ACCT_BAL_CNY
INTO UPD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
END P1


cobal code


*---------------------------------------------------------------*
* IBM SVL MINI BANKING SYSTEM *
*---------------------------------------------------------------*
*---------------------------------------------------------------*
* PROGRAM NAME : CALL A STORED PROCEDURE UPDBAL *
* PROGRAM ID : CALLSP *
* INTERFACE AREA : N/A *
* DATABASE USAGE : BINKDB *
* TABLE USAGE : ACCOUNT *
* FILE USAGE : N/A *
* FUNCTION : CALL A STORED PROCEDURE UDPBAL *
*---------------------------------------------------------------*
* PREPARED BY : JINSONG FENG *
* DATE WRITTEN : APR. 2009 *
* REVIEWED BY : JINSONG FENG *
* DATE MODIFIED : APR. 2009 *
*---------------------------------------------------------------*

*---------------------------------------------------------------*
* I D E N T I F I C A T I O N D I V I S I O N *
*---------------------------------------------------------------*
IDENTIFICATION DIVISION.
PROGRAM-ID. CALLSP.
AUTHOR. SVL.

*---------------------------------------------------------------*
* E N V I R O N M E N T D I V I S I O N *
*---------------------------------------------------------------*
ENVIRONMENT DIVISION.

*---------------------------------------------------------------*
* D A T A D I V I S I O N *
*---------------------------------------------------------------*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-ACCT-ID PIC X(10) VALUE '0000000001'.
01 WS-RATE PIC S9 COMP VALUE +2.
01 WS-OLD-BAL PIC S9(10)V9(2) USAGE COMP-3.
01 WS-NEW-BAL PIC S9(10)V9(2) USAGE COMP-3.
*---------------------------------------------------------------*
* SQL INCLUDE FOR SQLDA / SQLCA *
*---------------------------------------------------------------*
EXEC SQL INCLUDE SQLDA END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCOUNT END-EXEC.

LINKAGE SECTION.
*---------------------------------------------------------------*
* P R O C E D U R E D I V I S I O N *
*---------------------------------------------------------------*
PROCEDURE DIVISION.
MAIN SECTION.
DISPLAY "START TO CALL STORED PROCEDURE."

EXEC SQL
CALL ZUSER02.UPDBAL(:WS-ACCT-ID
,:WS-RATE
,:WS-OLD-BAL
,:WS-NEW-BAL
)
END-EXEC

DISPLAY "FOR ACCOUNT : ", WS-ACCT-ID
DISPLAY "THE OLD BALANCE IS : ", WS-OLD-BAL
DISPLAY "THE NEW BALANCE IS : ", WS-NEW-BAL
STOP RUN.



Presumably you don't have access to a mainframe to try it out.

If your Cobol (note the spelling) program is to test your stored procedure, you need to do a little more than use one piece of data. What happens with a "not found"? Do the other options work (1 and any)? Note that your assignment has some ambiguity. You are told 1, 2 and 3 for rate, then told not (1 or 2) for the 0.5% option. So, you should try it out with, say, a "5" - to know what happens.

I can't say much about the SQL, because I don't know much about it. But...
     SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.02)


will "work", but is not good. OLD_BAL has just been set to ACCT_BAL_CNY, but other than that, there is no reason to use OLD_BAL. If you want 2%, just multiply by 1.02, you don't need an add, and then a multipy by 0.02. And what about rounding?
If ACCT_BAL_CNY is 99.49 what is your answer, and what would it be with "rounding"? If rounding is automatically included, OK.

Now the Cobol. These are mostly my views, you can take them or leave them.

I hate the comments. 15 of them you can just get rid of, as they state the obvious. In the first block of comments, which do actually have some use, you have an error. That is one of the problems with comments. Another is that if you have lots of superfluous comments, the reader will tend to ignore them. Then if there is one important comment in the ptogram, chances are it won't get read. Whereas, if there is one comment in the program, because it is important, chances are it will not be possible to avoid reading it.

WS-RATE, should be PIC S9(4) for me. For your version, the compiler is still allocating 2 bytes of storage (a half-word), so there is no "saving" there. The compiler will (depending on the OPTIONS you give it) generate code to ensure that each time the field is updated, it can only have one digit. A waste. Not much here, but process a million records daily and the little things add up. Don't get into bad habits.

But, you say, it can only be one character, why allow four digits? Well, I wouldn't. For such a thing, why would it be binary (COMP) at all? Why not PIC X?

You have your balances, ie 01 WS-OLD-BAL PIC S9(10)V9(2) USAGE COMP-3. COMP-3 always works best with an ODD number of digits. Because otherewise (depending on the OPTIONS...) the compiler will generate code to ensure the left-most (high-order) half-byte can only contain X'0'. Every time you change the value in the field. A waste (see above).

Plus, what is the SQL doing with the definition? Might something be getting in that left-most half-byte? You don't know? (I don't). Test it. See. Look it up in the manual. Confirm. If something can get in there, it would be best not to loose it, anyway.

Why don't you display the WS-RATE at the end. You can see the figures have changed, OK, but have they changed correctly?

If you are going to do DISPLAYs for information, one thing I always found useful was to display the date/time of the compile. Then you will always be able to associate your test output with the actual compile listing (identical date/time). Look up WHEN-COMPILED in the Cobol manual, just format it a little, and there you go. It'll be like giving an apple to the teacher :-)

Another of my personal things, for displays, is to show the length of the field, by "bounding" the displayed items with greater-than and less-than symbols. ie

    DISPLAY "A IS>" A "<> B IS >" B "<"


At times, strange things will happen to your data, which, when you display them, you don't see because they are "non-display" characters (like a space, for instance, low-values, high-values, lots of other things). This technique avoids you staring at your DISPLAY output for hours, wondering what is wrong, when all the time there is a space in the right-most character.

Also nice, when you upgrade your program with better test data, is to count the number of executions and display that. Here, it might mean nothing, but it is a good habbit. If you count every time you read, and every time you write, and output those counts by file, (even with some hash-totals) then you won't lose or duplicate records without knowing it.