Page 1 of 2

Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 9:25 pm
by ananya1212
Hi,

I'm developing a code wherein my table structure is as follows --

CREATE TABLE TRANSACTION(                                             
  TRANSID        CHAR(6)       NOT NULL,                               
  TRANSTIME      TIME          NOT NULL WITH DEFAULT,                   
  TRANSDATE      DATE          NOT NULL WITH DEFAULT,                   
  TRANSAMT       DECIMAL(10,2) NOT NULL,                               
  TRANSTYPE      CHAR(2)       CHECK(TRANSTYPE IN('DA','WA','AT')),     
  ACCOUNTNO      NUMERIC(12),                                         
  TOACCOUNTNO    NUMERIC(12),                                         
  CONSTRAINT TRANS_FKEY1 FOREIGN KEY(ACCOUNTNO) REFERENCES             
     ACCOUNTS(ACCOUNTNO),                                             
  CONSTRAINT TRANS_FKEY2 FOREIGN KEY(TOACCOUNTNO) REFERENCES           
     ACCOUNTS(ACCOUNTNO),                                             
  CONSTRAINT TRANS_PKEY PRIMARY KEY(TRANSID)                           
  )IN db1.ts1 ;

Now i m passing 2 values to the table as
Accountno and balance in jcl as

//SYSIN DD *                                                           
100000000001                                                           
0000005000                                                             
/*


Th value is not getting inserted in the table . Sql code error -310 is displayed.
Please help.

The pic clause of accountno and balance is declared in cobol pgm as

01  WS-ACCOUNTNO                PIC 9(12).                             
                                                                       
 01  WS-WITHDRAW                 PIC 9(8)V99.



Please let me know where m I going wrong. :(

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 9:49 pm
by Akatsukami
Read the fine manual on SQLCODE = -310 and see if you can figure out from there (hint: read the "Programmer Response", several times if need be).

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 10:33 pm
by NicC
Welcome to the forum. Please, when posting code or data, use the code tags. (I have done his for you this time.) You can search the forum to find out how to use them but if you use the full editor it should be pretty obvious - highlight the text to be coded and click the 'Code' button. The blue on black does not happen without you doing so. It also maintains spacing thus making your code much more readable.

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:30 pm
by ananya1212
Thanks for the code tags NicC. I'm new to this forum.
Please can someone help me out with this problem...please..I need the solution asap :(

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:40 pm
by ananya1212
I just want someone to cross check if the value i'm oassing through JCL is correct as per the pic clause and table structure.
If not,what values should be passed.

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:40 pm
by Robert Sample
I am not a DB2 person but it seems you have declared TRANSAMT as NOT NULL and yet you do not tell us what value you provided in your program for this variable. Since it is a DECIMAL value, perhaps the lack of a value would cause your SQL return code?

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:47 pm
by ananya1212
Hi.Thanks for your reply Robert .The table transaction contains various columns like (transid,transtime,transdate,transamt,transtype,accountno,toaccountno)

I am autogenerating the transid in my program. Transtime and trainsdate are set with default values. Toaccountno is set as null (through null value indicator concept)
Now I have to accept accountno and transamt through JCL and insert all these value in COBOL pgm.
This is the part where I'm getting this error.
Hope i have made myself clear.
:)

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:54 pm
by Akatsukami
:roll: Ananya-chan, did you read that part of the documentation that said, "Ensure that all decimal variables or parameters contain valid System/370 packed decimal numbers"? What is the COBOL USAGE clause that corresponds to packed decimal?

Re: Facing problem with decimal values in table

PostPosted: Wed Aug 14, 2013 11:57 pm
by ananya1212
is it COMP 1 ?

Re: Facing problem with decimal values in table

PostPosted: Thu Aug 15, 2013 12:18 am
by Akatsukami
Without wishing to be overly critical, Ananya-chan, I suggest that that answer means that you ought not to be listing COBOL as being among your skills.

No, a packed decimal variable will be defined USAGE COMP-3 in COBOL (COMP-1 is short floating-point). As your variables are not so defined, I opine that that is your problem. I recommend that you code something like:

01  WS-EXTERNAL-VARIABLES.
    05  WS-EXT-ACCOUNT-NO   PIC 9(12).
    05  WS-EXT-XACTION-AMT  PIC 9(8)V99.
01  WS-INTERNAL-VARIABLES.
    05  WS-INT-ACCOUNT-NO   PIC 9(12)    COMP-3.
    05  WS-INT-XACTION-AMT  PIC S9(8)V99 COMP-3  .
  :
  :
MOVE WS-EXT-ACCOUNT-NO  TO WS-INT-ACCOUNT-NO.
MOVE WS-EXT-XACTION-AMT TO WS-INT-XACTION-AMT.

and use the "internal" variables for your insert. There are other problems here, but they amount to poor design rather than outright error.