SIMPLE SQL STATEMENT
HELP
I HAVE a table with duplicated records .this was a result of a aplication
progmamme which inserted duplicates in stead of updating?
i want to remove the duplicate and use some of the duplicate info to update
..
eg
recno name regnomber course
1 simba 345 ct12
2 john 356 ct13
3 simba 345 ct14
4 try 999 ct67
5 andy 444 ct66
6 john 356 ct78
what i want to do
1 update record 1 with ct14
2 remove record 3
ffor 3000 records so i want sql statement or a 4gl programme but i know this
might abe a newbie question but ...bear with me i need this
-----Original Message-----
From: Mark D. Stock [mailto:mdstock@mydas.freeserve.co.uk]
Sent: Wednesday, November 22, 2000 1:50 AM
To: Andrew Ford
Cc: Informix-List (E-mail)
Subject: Re: You've heard it all before, cont.
Andrew Ford wrote:
>
> I'm posting some other onstat commands that may help.
>
> Informix Dynamic Server Version 7.31.FC4 -- On-Line -- Up 3 days
09:44:13 -- 2545024 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 261671478 74154240 9591835411 97.27 55502789 51443264 212025414 73.82
>
> isamtot open start read write rewrite delete commit
rollbk
> 8587085144 43126935 172567752 4682012886 56281592 10069964 3147447
22054673 42895
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 1187 151984.04 65843.13 967 1934
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 16758474 48898 5081207577 0 0 18077 2029819 642171
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 92314484 3084372 1919689 97237545 3309121
Definitely short of buffers. I would also guess that your LRU parameters
are too low, unless they were lowered to improve checkpoint times.
> Sorry about that, UPDATE STATS was run (I even used Art's program to do
it).
That's okay then.
> Another thing I would like to add.
>
> VP Scheduler Statistics:
> vp pid class semops busy waits spins/wait
> 1 28505 cpu 17491 17534 1000
> 2 28506 adm 0 0 0
> 3 28507 cpu 20517 20586 999
> 4 28508 cpu 19405 19467 999
> 5 28509 cpu 18285 18334 999
> 6 28510 cpu 69192125 69325060 999
> 7 28511 cpu 56643372 56765184 999
> 8 28512 cpu 44975001 45084655 999
> 9 28513 lio 2 0 0
> 10 28514 pio 2 0 0
> 11 28515 aio 146 0 0
> 12 28516 msc 30523 0 0
>
>
> There are a lot of semops and busy waits on VP's 6,7,8. Is this ok
> since the spins/wait are at or near 1000?
>
> MT global info:
> sessions threads vps lngspins
> 114 324 12 434
>
> sched calls thread switches yield 0 yield n yield
forever
> total: 1913107135 1490724804 426169445 16877225 694531053
> per sec: 0 0 0 0 0
>
> Virtual processor summary:
> class vps usercpu syscpu total
> cpu 7 151956.74 65772.65 217729.39
> aio 1 2.74 12.17 14.91
> lio 1 2.12 7.70 9.82
> pio 1 2.09 7.85 9.94
> adm 1 14.27 34.33 48.60
> msc 1 6.08 8.43 14.51
> total 12 151984.04 65843.13 217827.17
>
> Individual virtual processors:
> vp pid class usercpu syscpu total
> 1 28505 cpu 27636.92 18176.92 45813.84
> 2 28506 adm 14.27 34.33 48.60
> 3 28507 cpu 25982.39 16773.92 42756.31
> 4 28508 cpu 30565.32 17020.38 47585.70
> 5 28509 cpu 16064.93 3393.09 19458.02
> 6 28510 cpu 20905.27 4161.71 25066.98
> 7 28511 cpu 17043.99 3418.54 20462.53
> 8 28512 cpu 13757.92 2828.09 16586.01
> 9 28513 lio 2.12 7.70 9.82
> 10 28514 pio 2.09 7.85 9.94
> 11 28515 aio 2.74 12.17 14.91
> 12 28516 msc 6.08 8.43 14.51
> tot 151984.04 65843.13 217827.17
Is this a DB server only or are you running other apps on this box? If
you are, then I would remove about 3 CPU VPs for starters. You would
have to alter corresponding parameters like NETTYPE.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+
Simbarashe.Pangeti@icl.com wrote: > HELP > I HAVE a table with duplicated records .this was a result of a aplication > progmamme which inserted duplicates in stead of updating? > i want to remove the duplicate and use some of the duplicate info to update > .. > > eg > recno name regnomber course > 1 simba 345 ct12 > 2 john 356 ct13 > 3 simba 345 ct14 > 4 try 999 ct67 > 5 andy 444 ct66 > 6 john 356 ct78 > > what i want to do > 1 update record 1 with ct14 > 2 remove record 3 > > ffor 3000 records so i want sql statement or a 4gl programme but i know this > might abe a newbie question but ...bear with me i need this > Your requirement is not clear : What is the business rule that demands that recno =1 should be updated to ct14? Is it that 'ct14' is greater than 'ct12' or is it that the recno is higher and therefore the information is more current? What about recno 2 and 6? Does 2 have to be updated to ct78? Why does recno 1 have to be updated to ct14? Can't it just be deleted, leaving recno 3 as is to represent simba's selection? Rudy
The real problem is that this table should have a PRIMARY KEY constraint,
a UNIQUE constraint, or AT LEAST a UNIQUE index on regnomber to prevent the
insertion of duplicate rows. There is of course no easy way to do this.
You will have to manually perform the deletes and updates. (BTW why not
just delete the ctl2 record and keep the existing ctl4 record instead of
deleting the latter and updating the former?) Finding the dups is easy:
SELECT name, regnomber, COUNT(*)
FROM mytable
GROUP BY 1,2
HAVING COUNT(*) > 1
ORDER BY 2;
If you have ISQL create a quick form and use that to perform the deletes
and updates once you have the list of regnombers that are duplicated.
Art S. Kagel
Simbarashe.Pangeti@icl.com wrote:
>
> HELP
> I HAVE a table with duplicated records .this was a result of a aplication
> progmamme which inserted duplicates in stead of updating?
> i want to remove the duplicate and use some of the duplicate info to update
> ..
>
> eg
> recno name regnomber course
> 1 simba 345 ct12
> 2 john 356 ct13
> 3 simba 345 ct14
> 4 try 999 ct67
> 5 andy 444 ct66
> 6 john 356 ct78
>
> what i want to do
> 1 update record 1 with ct14
> 2 remove record 3
>
> ffor 3000 records so i want sql statement or a 4gl programme but i know this
> might abe a newbie question but ...bear with me i need this
>
>
> -----Original Message-----
> From: Mark D. Stock [mailto:mdstock@mydas.freeserve.co.uk]
> Sent: Wednesday, November 22, 2000 1:50 AM
> To: Andrew Ford
> Cc: Informix-List (E-mail)
> Subject: Re: You've heard it all before, cont.
>
> Andrew Ford wrote:
> >
> > I'm posting some other onstat commands that may help.
> >
> > Informix Dynamic Server Version 7.31.FC4 -- On-Line -- Up 3 days
> 09:44:13 -- 2545024 Kbytes
> >
> > Profile
> > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > 261671478 74154240 9591835411 97.27 55502789 51443264 212025414 73.82
> >
> > isamtot open start read write rewrite delete commit
> rollbk
> > 8587085144 43126935 172567752 4682012886 56281592 10069964 3147447
> 22054673 42895
> >
> > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> > 0 0 0 0 0 0 0
> >
> > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> > 0 0 1187 151984.04 65843.13 967 1934
> >
> > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> > 16758474 48898 5081207577 0 0 18077 2029819 642171
> >
> > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > 92314484 3084372 1919689 97237545 3309121
>
> Definitely short of buffers. I would also guess that your LRU parameters
> are too low, unless they were lowered to improve checkpoint times.
>
> > Sorry about that, UPDATE STATS was run (I even used Art's program to do
> it).
>
> That's okay then.
>
> > Another thing I would like to add.
> >
> > VP Scheduler Statistics:
> > vp pid class semops busy waits spins/wait
> > 1 28505 cpu 17491 17534 1000
> > 2 28506 adm 0 0 0
> > 3 28507 cpu 20517 20586 999
> > 4 28508 cpu 19405 19467 999
> > 5 28509 cpu 18285 18334 999
> > 6 28510 cpu 69192125 69325060 999
> > 7 28511 cpu 56643372 56765184 999
> > 8 28512 cpu 44975001 45084655 999
> > 9 28513 lio 2 0 0
> > 10 28514 pio 2 0 0
> > 11 28515 aio 146 0 0
> > 12 28516 msc 30523 0 0
> >
> >
> > There are a lot of semops and busy waits on VP's 6,7,8. Is this ok
> > since the spins/wait are at or near 1000?
> >
> > MT global info:
> > sessions threads vps lngspins
> > 114 324 12 434
> >
> > sched calls thread switches yield 0 yield n yield
> forever
> > total: 1913107135 1490724804 426169445 16877225 694531053
> > per sec: 0 0 0 0 0
> >
> > Virtual processor summary:
> > class vps usercpu syscpu total
> > cpu 7 151956.74 65772.65 217729.39
> > aio 1 2.74 12.17 14.91
> > lio 1 2.12 7.70 9.82
> > pio 1 2.09 7.85 9.94
> > adm 1 14.27 34.33 48.60
> > msc 1 6.08 8.43 14.51
> > total 12 151984.04 65843.13 217827.17
> >
> > Individual virtual processors:
> > vp pid class usercpu syscpu total
> > 1 28505 cpu 27636.92 18176.92 45813.84
> > 2 28506 adm 14.27 34.33 48.60
> > 3 28507 cpu 25982.39 16773.92 42756.31
> > 4 28508 cpu 30565.32 17020.38 47585.70
> > 5 28509 cpu 16064.93 3393.09 19458.02
> > 6 28510 cpu 20905.27 4161.71 25066.98
> > 7 28511 cpu 17043.99 3418.54 20462.53
> > 8 28512 cpu 13757.92 2828.09 16586.01
> > 9 28513 lio 2.12 7.70 9.82
> > 10 28514 pio 2.09 7.85 9.94
> > 11 28515 aio 2.74 12.17 14.91
> > 12 28516 msc 6.08 8.43 14.51
> > tot 151984.04 65843.13 217827.17
>
> Is this a DB server only or are you running other apps on this box? If
> you are, then I would remove about 3 CPU VPs for starters. You would
> have to alter corresponding parameters like NETTYPE.
>
> Cheers,
> --
> Mark.
>
> +----------------------------------------------------------+-----------+
> | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
> | http://www.informix.com http://www.informixhandbook.com |///// / //|
> | http://www.iiug.org +-----------------------------------+//// / ///|
> | |This email will self-destruct in |/// / ////|
> | |10 sec. If you received this email |// / /////|
> | |in error, sorry about the mess. |/ ////////|
> +----------------------+-----------------------------------+-----------+