IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

need to replase the value in table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Fri Jul 30, 2010 1:49 pm
Reply with quote

I have one table A. Table A having column A1 and A2.

Column A1 values example below...

A1
-----------

xsvaaaa
xsvbbbb
xsvhjhjh
xsvuyre
sssgtree
cdvcesd
cxdvvvv
drerttyrd
gfvrtyuer
bfvretytr

I need to change the value XSV replacing to YSV in colum A1.

colum A1 having 1 lak records , but XSV* records are 10 thousends records, i need to insert this 10 thousend records to table A.

This XSV value and YSV values are need to pass from out side the program.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Jul 30, 2010 1:56 pm
Reply with quote

What have you tried so far and what were the results that caused you a problem.
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Fri Jul 30, 2010 2:12 pm
Reply with quote

i have tried, but i need to fix immed pls give me the logic... thru cobol
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Jul 30, 2010 2:18 pm
Reply with quote

george16 wrote:
i have tried, but i need to fix immed pls give me the logic... thru cobol
Please do not ask for code for urgent fixes. Unfortunately the urgency is yours alone.

I assume that this is a DB2 table, but this is something that you need to state clearly. What have you tried, please post the code so that the SQL wizards can see where you are coming from and be better able to help you. Also the results of your attempt may be of value. The fact that you say that you have tried is irrelevant, because without knowing what you have tried there is always the possibility that the same suggestion could be made again which would waste everyones time.

Please give us some real detail to be able to work with to help you out.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 30, 2010 2:36 pm
Reply with quote

george16,
not for the first time, your requirements are confusing.

1. is column-a part of an index? if so, which type?

2. do you want to replace (UPDATE) the existing, or
do you want to add (INSERT) additional rows with a new column-a,
where ... has been changed to ---?

3. are there other columns? what is the disposition of these columns?
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Fri Jul 30, 2010 4:01 pm
Reply with quote

1. pic X(8).
2. i want to add (INSERT)
3.A1 coulmn is the key.so we can insert new name
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 30, 2010 5:53 pm
Reply with quote

untested code

Code:

01  parms.
  05  new-val      pic x(03).
  05  old-val       pic x(03).

read parms

exec sql
  insert into tableA
    select(concat(:new-val,substr(a1,4,5)),a2 from tableA)
  where substr(a1,1,3) = :old-val
end-exec


or
Code:


05  counter       pic s9(7) comp-3.
   88  time-to-commit  value 1000.
   88  no-work-done    value zero.

start-routine  section.

  exec sql
    declare cursor dbz with hold for select
      a1,
      a2
    from tableA
    where substr(a1,1,3) = :old-val
  end-exec

  exec sql
    open dbz
  end-exec

  perform fetch-and-insert-control
       until sqlcode <> 0

  perform commit-routine
  .
section-exit.
exit.

fetch-and-insert-control   section.

  exec sql
    fetch dbz
      into
        :host-a1,
        :host-a2
  end-exec

  if sqlcode = 0
  then
    compute counter = counter + 1
    move new-val to hold-a1(1:3)
    perform insert-routine
    if time-to-commit
      perform commit-routine
    end-if
  end-if
  .
section-exit.
exit

insert-routine section.

  exec sql
    insert into
    tablea (a1,a2)
    values(:host-a1,:host-a2)
  end-exec
.
section-exit.
exit.

commit-routine  section.

  exec sql
    commit work
  end-exec

  set no-work-done to true
  .
section-exit.
exit.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top