All 23 messages in topic - view as tree
From: Henry - view profile
Date: Thurs, Dec 7 2006 6:41 pm
Email: "Henry"
Hi gurus:
I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs
HP-UX server.
There are 57 dbspaces with a total of 85 chunks.
The informix engine is running very slow recently.
I was not sure whether it is the result of poor tuning or not.
Can you please help me to check and give me some recommendations on how
to tune it in order to get the best performance?
Thanks a lot for your help!
Here is my Ifx environment:
NUMCPUVPS 5
RESIDENT 0
BUFFERS 500000
NUMAIOVPS 10
PHYSBUFF 64
LOGBUFF 32
CLEANERS 16
LRUS 16
RA_PAGES 128
RA_THRESHOLD 120
MAX_PDQPRIORITY 100
DS_MAX_QUERIES 6
DS_TOTAL_MEMORY 300000
DS_MAX_SCANS 1048576
OPTCOMPIND 1
------------------------------------------------------------------------------------------------------------------------
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
errors
msc 0 i 0.1 258826 0 0 0 258285 1.0
0
aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1
0
aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2
0
aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3
0
aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5
0
aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7
0
aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9
0
aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1
0
aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4
0
aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8
0
aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2
0
pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0
0
lio 0 i 0.0 81620 0 81620 0 80652 1.0
0
------------------------------------------------------------------------------------------------------------------------
16 buffer LRU queue sets priority levels
# type set total % of length LOW HIGH
0 F 31229 99.8% 31160 22957 8203
1 m 0.2% 69 37 32
2 f 31250 99.8% 31190 22987 8203
3 m 0.2% 60 36 24
4 f 31230 99.8% 31163 22960 8203
5 m 0.2% 67 36 31
6 f 31271 99.8% 31195 22992 8203
7 m 0.2% 76 43 33
8 f 31247 99.8% 31189 22986 8203
9 m 0.2% 58 36 22
10 f 31238 99.8% 31171 22968 8203
11 m 0.2% 67 39 28
12 f 31271 99.8% 31194 22991 8203
13 m 0.2% 77 33 44
14 f 31254 99.8% 31181 22978 8203
15 m 0.2% 73 40 33
16 f 31245 99.8% 31170 22967 8203
17 m 0.2% 75 39 36
18 f 31245 99.7% 31165 22962 8203
19 m 0.3% 80 51 29
20 f 31243 99.8% 31171 22968 8203
21 m 0.2% 72 44 28
22 f 31265 99.8% 31199 22996 8203
23 m 0.2% 66 40 26
24 f 31241 99.8% 31169 22966 8203
25 m 0.2% 72 34 38
26 f 31264 99.8% 31192 22989 8203
27 m 0.2% 72 44 28
28 f 31237 99.7% 31156 22953 8203
29 m 0.3% 81 37 44
30 f 31269 99.8% 31205 23002 8203
31 m 0.2% 64 39 25
1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048
buffer size
start clean at 70.000% (of set total) dirty, or 21875 buffs dirty,
stop at
60.000%
------------------------------------------------------------------------------------------------------------------------
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25
days 18:43:11 -- 1602308 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2632097402 4941899304 45398348353 94.20 311280009 1399748080
2450843309 92.79
isamtot open start read write rewrite delete commit
rollbk
28871389253 72735451 1442312012 19817366922 589835341 138529174
205634855 294588 0
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 0 2812432.82 626212.81 7201 14402
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
66194052 2043 3691556477 0 0 13524 49590653
34601364
ixda-RA idx-RA da-RA RA-pgsused lchwaits
402916999 38263349 1719398752 2144011894 26815459
------------------------------------------------------------------------------------------------------------------------
THanks again for your help!
Reply » Rate this post: Text for clearing space
From: Superboer - view profile
Date: Thurs, Dec 7 2006 6:55 pm
Email: "Superboer"
what happened?? reloading data??? update statistics???
define slow. and also supply how busy your disks and cpus are.
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 66194052 2043 3691556477 0 0 13524 49590653
> 34601364
seq scans seems high???
may be > OPTCOMPIND 1 set to 0?? have you run update statistics??
> start clean at 70.000% (of set total) dirty, or 21875 buffs dirty,
> stop at
> 60.000%
LRU_MAX and min dirty set to 60 and 70 %???? what is the reason for
that???
may be 6 and 7 or 5 and 10....
do you use KAIO or just iovps??? i suggest KAIO nowadays!!!
if you must use iovps i would add at least 10, maybe i would create
58 aivps... hummmm you are better off using kaio.
Superboer
Henry schreef:
- Hide quoted text -
- Show quoted text -
> Hi gurus:
> I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs
> HP-UX server.
> There are 57 dbspaces with a total of 85 chunks.
> The informix engine is running very slow recently.
> I was not sure whether it is the result of poor tuning or not.
> Can you please help me to check and give me some recommendations on how
> to tune it in order to get the best performance?
> Thanks a lot for your help!
> Here is my Ifx environment:
> NUMCPUVPS 5
> RESIDENT 0
> BUFFERS 500000
> NUMAIOVPS 10
> PHYSBUFF 64
> LOGBUFF 32
> CLEANERS 16
> LRUS 16
> RA_PAGES 128
> RA_THRESHOLD 120
> MAX_PDQPRIORITY 100
> DS_MAX_QUERIES 6
> DS_TOTAL_MEMORY 300000
> DS_MAX_SCANS 1048576
> OPTCOMPIND 1
> ------------------------------------------------------------------------------------------------------------------------
> AIO I/O vps:
> class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
> errors
> msc 0 i 0.1 258826 0 0 0 258285 1.0
> 0
> aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1
> 0
> aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2
> 0
> aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3
> 0
> aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5
> 0
> aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7
> 0
> aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9
> 0
> aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1
> 0
> aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4
> 0
> aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8
> 0
> aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2
> 0
> pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0
> 0
> lio 0 i 0.0 81620 0 81620 0 80652 1.0
> 0
> ------------------------------------------------------------------------------------------------------------------------
> 16 buffer LRU queue sets priority levels
> # type set total % of length LOW HIGH
> 0 F 31229 99.8% 31160 22957 8203
> 1 m 0.2% 69 37 32
> 2 f 31250 99.8% 31190 22987 8203
> 3 m 0.2% 60 36 24
> 4 f 31230 99.8% 31163 22960 8203
> 5 m 0.2% 67 36 31
> 6 f 31271 99.8% 31195 22992 8203
> 7 m 0.2% 76 43 33
> 8 f 31247 99.8% 31189 22986 8203
> 9 m 0.2% 58 36 22
> 10 f 31238 99.8% 31171 22968 8203
> 11 m 0.2% 67 39 28
> 12 f 31271 99.8% 31194 22991 8203
> 13 m 0.2% 77 33 44
> 14 f 31254 99.8% 31181 22978 8203
> 15 m 0.2% 73 40 33
> 16 f 31245 99.8% 31170 22967 8203
> 17 m 0.2% 75 39 36
> 18 f 31245 99.7% 31165 22962 8203
> 19 m 0.3% 80 51 29
> 20 f 31243 99.8% 31171 22968 8203
> 21 m 0.2% 72 44 28
> 22 f 31265 99.8% 31199 22996 8203
> 23 m 0.2% 66 40 26
> 24 f 31241 99.8% 31169 22966 8203
> 25 m 0.2% 72 34 38
> 26 f 31264 99.8% 31192 22989 8203
> 27 m 0.2% 72 44 28
> 28 f 31237 99.7% 31156 22953 8203
> 29 m 0.3% 81 37 44
> 30 f 31269 99.8% 31205 23002 8203
> 31 m 0.2% 64 39 25
> 1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048
> buffer size
> start clean at 70.000% (of set total) dirty, or 21875 buffs dirty,
> stop at
> 60.000%
> ------------------------------------------------------------------------------------------------------------------------
> IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25
> days 18:43:11 -- 1602308 Kbytes
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 2632097402 4941899304 45398348353 94.20 311280009 1399748080
> 2450843309 92.79
> isamtot open start read write rewrite delete commit
> rollbk
> 28871389253 72735451 1442312012 19817366922 589835341 138529174
> 205634855 294588 0
> 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 0 2812432.82 626212.81 7201 14402
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 66194052 2043 3691556477 0 0 13524 49590653
> 34601364
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 402916999 38263349 1719398752 2144011894 26815459
> ------------------------------------------------------------------------------------------------------------------------
> THanks again for your help!
Reply » Rate this post: Text for clearing space
From: scottishpoet - view profile
Date: Thurs, Dec 7 2006 6:58 pm
Email: "scottishpoet"
percentage read cached 94%
could do better, can you afford to give more nemory to inormix?
seq scans seems high, badly written queries, poor indexes, poor
statistics for the optimiser?
if you have lots of seq scans this may explain the read cache
Reply » Rate this post: Text for clearing space
From: Obnoxio The Clown - view profile
Date: Thurs, Dec 7 2006 7:14 pm
Email: "Obnoxio The Clown"
Henry said:
> Hi gurus:
> I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs
> HP-UX server.
> There are 57 dbspaces with a total of 85 chunks.
> The informix engine is running very slow recently.
What changed? Also, you need more buffers.
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Reply » Rate this post: Text for clearing space
From: Darren_Jac...@carmax.com - view profile
Date: Thurs, Dec 7 2006 7:59 pm
Email: Darren_Jac...@carmax.com
What type of system - DSS or OLTP?
What does top, yamm, glance, etc indicate? High CPU on all 6?
What are your checkpoints?
What's your virtsize?
What is the physical size of mem on the box?
What type of storage?
Do you have a hot disk?
Raw or Cooked?
onstat -u
onstat -m
onstat -g seg
Have you identified any long running queries?
Have you looked at the set explain on any queries?
Do you use directives?
And yes, have you tried update statistics? No stats-the optimizer has no
idea how the data is distributed in your tables and decides to perform
scans.
Thanks
"Superboer"
Sent by: informix-l...@iiug.org
informix-list-bou cc
n...@iiug.org
Subject
Re: Pls help to tune my informix
12/07/2006 11:55 engine .. thanks
AM
what happened?? reloading data??? update statistics???
define slow. and also supply how busy your disks and cpus are.
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 66194052 2043 3691556477 0 0 13524 49590653
> 34601364
seq scans seems high???
may be > OPTCOMPIND 1 set to 0?? have you run update statistics??
> start clean at 70.000% (of set total) dirty, or 21875 buffs dirty,
> stop at
> 60.000%
LRU_MAX and min dirty set to 60 and 70 %???? what is the reason for
that???
may be 6 and 7 or 5 and 10....
do you use KAIO or just iovps??? i suggest KAIO nowadays!!!
if you must use iovps i would add at least 10, maybe i would create
58 aivps... hummmm you are better off using kaio.
Superboer
Henry schreef:
- Hide quoted text -
- Show quoted text -
> Hi gurus:
> I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs
> HP-UX server.
> There are 57 dbspaces with a total of 85 chunks.
> The informix engine is running very slow recently.
> I was not sure whether it is the result of poor tuning or not.
> Can you please help me to check and give me some recommendations on how
> to tune it in order to get the best performance?
> Thanks a lot for your help!
> Here is my Ifx environment:
> NUMCPUVPS 5
> RESIDENT 0
> BUFFERS 500000
> NUMAIOVPS 10
> PHYSBUFF 64
> LOGBUFF 32
> CLEANERS 16
> LRUS 16
> RA_PAGES 128
> RA_THRESHOLD 120
> MAX_PDQPRIORITY 100
> DS_MAX_QUERIES 6
> DS_TOTAL_MEMORY 300000
> DS_MAX_SCANS 1048576
> OPTCOMPIND 1
------------------------------------------------------------------------------------------------------------------------
- Hide quoted text -
- Show quoted text -
> AIO I/O vps:
> class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
> errors
> msc 0 i 0.1 258826 0 0 0 258285 1.0
> 0
> aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1
> 0
> aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2
> 0
> aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3
> 0
> aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5
> 0
> aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7
> 0
> aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9
> 0
> aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1
> 0
> aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4
> 0
> aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8
> 0
> aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2
> 0
> pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0
> 0
> lio 0 i 0.0 81620 0 81620 0 80652 1.0
> 0
------------------------------------------------------------------------------------------------------------------------
- Hide quoted text -
- Show quoted text -
> 16 buffer LRU queue sets priority levels
> # type set total % of length LOW HIGH
> 0 F 31229 99.8% 31160 22957 8203
> 1 m 0.2% 69 37 32
> 2 f 31250 99.8% 31190 22987 8203
> 3 m 0.2% 60 36 24
> 4 f 31230 99.8% 31163 22960 8203
> 5 m 0.2% 67 36 31
> 6 f 31271 99.8% 31195 22992 8203
> 7 m 0.2% 76 43 33
> 8 f 31247 99.8% 31189 22986 8203
> 9 m 0.2% 58 36 22
> 10 f 31238 99.8% 31171 22968 8203
> 11 m 0.2% 67 39 28
> 12 f 31271 99.8% 31194 22991 8203
> 13 m 0.2% 77 33 44
> 14 f 31254 99.8% 31181 22978 8203
> 15 m 0.2% 73 40 33
> 16 f 31245 99.8% 31170 22967 8203
> 17 m 0.2% 75 39 36
> 18 f 31245 99.7% 31165 22962 8203
> 19 m 0.3% 80 51 29
> 20 f 31243 99.8% 31171 22968 8203
> 21 m 0.2% 72 44 28
> 22 f 31265 99.8% 31199 22996 8203
> 23 m 0.2% 66 40 26
> 24 f 31241 99.8% 31169 22966 8203
> 25 m 0.2% 72 34 38
> 26 f 31264 99.8% 31192 22989 8203
> 27 m 0.2% 72 44 28
> 28 f 31237 99.7% 31156 22953 8203
> 29 m 0.3% 81 37 44
> 30 f 31269 99.8% 31205 23002 8203
> 31 m 0.2% 64 39 25
> 1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048
> buffer size
> start clean at 70.000% (of set total) dirty, or 21875 buffs dirty,
> stop at
> 60.000%
------------------------------------------------------------------------------------------------------------------------
- Hide quoted text -
- Show quoted text -
> IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25
> days 18:43:11 -- 1602308 Kbytes
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 2632097402 4941899304 45398348353 94.20 311280009 1399748080
> 2450843309 92.79
> isamtot open start read write rewrite delete commit
> rollbk
> 28871389253 72735451 1442312012 19817366922 589835341 138529174
> 205634855 294588 0
> 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 0 2812432.82 626212.81 7201 14402
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 66194052 2043 3691556477 0 0 13524 49590653
> 34601364
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 402916999 38263349 1719398752 2144011894 26815459
------------------------------------------------------------------------------------------------------------------------
> THanks again for your help!
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Obnoxio The Clown - view profile
Date: Thurs, Dec 7 2006 8:36 pm
Email: "Obnoxio The Clown"
Darren_Jac...@carmax.com said:
> And yes, have you tried update statistics?
Cut the guy some slack. Not everyone is perfect like you.
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Reply » Rate this post: Text for clearing space
From: Darren_Jac...@carmax.com - view profile
Date: Thurs, Dec 7 2006 8:52 pm
Email: Darren_Jac...@carmax.com
Ha Ha Ha....Guess I'm not immune.
Kinda hard to perf tune without being on the box.
BTW, have your tried update statistics! 8-P
peace
"Obnoxio The
Clown"
Sent by: cc
informix-list-bou informix-l...@iiug.org
n...@iiug.org Subject
Re: Pls help to tune my informix
engine .. thanks
12/07/2006 01:36
PM
Darren_Jac...@carmax.com said:
> And yes, have you tried update statistics?
Cut the guy some slack. Not everyone is perfect like you.
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
Subject changed: Data Modeling?
From: Urich Ann - view profile
Date: Thurs, Dec 7 2006 9:00 pm
Email: "Urich Ann"
Is it a 'norm' that DBA's use a data model (both logical and physical)
to design a database?
or
Are data models mostly used as a 'post-design' afterthought, to document
what is physically already implemented?
In most shops that I have worked, we never used a model. And if they did
have a data model, it was never up-to-date.
The model was an after-thought for documentation purposes... It was done
after the table was implemented in Production.
Is there a good modeling tool for Informix 10?
Is there any one 'data modeling' design class that is better then
others?
Reply » Rate this post: Text for clearing space
From: Ian Michael Gumby - view profile
Date: Thurs, Dec 7 2006 10:53 pm
Email: "Ian Michael Gumby"
- Hide quoted text -
- Show quoted text -
>Is it a 'norm' that DBA's use a data model (both logical and physical)
>to design a database?
>or
>Are data models mostly used as a 'post-design' afterthought, to document
>what is physically already implemented?
>In most shops that I have worked, we never used a model. And if they did
>have a data model, it was never up-to-date.
>The model was an after-thought for documentation purposes... It was done
>after the table was implemented in Production.
>Is there a good modeling tool for Informix 10?
>Is there any one 'data modeling' design class that is better then
>others?
Now this is a loaded question...
Separate the logical from the physical.
A physical model helps the DBAs plan out how to lay out the data to get the
most bang for the buck.
The logical model helps when you need to determine the relationship between
tables, constraints , etc... The logical model helps those who grok database
design. Some shops swear by using models.
Others ignore them and treat the database as a persistence of objects.
IMHO, you'd be a fool not use a modeling tool. A good tool will shave time
off of development.
With respect to which tool, I've liked Erwin in the past, although the 3K
pricetag and I'm not sure if the latest release supports IDS makes it less
than desirable. (I support multiple platforms and RDBMSs)
I've downloaded a copy of the Rational Data Architect trial version.
(Actually Its going on in another window right now.) Its an eclipse plug in,
and I've noticed that you can click on DB2 support.
Didn't see IDS there.
Will let you know what I think of it. ...
_________________________________________________________________
Stay up-to-date with your friends through the Windows Live Spaces friends
list.
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=htt...
Reply » Rate this post: Text for clearing space
From: Obnoxio The Clown - view profile
Date: Fri, Dec 8 2006 12:09 am
Email: "Obnoxio The Clown"
Urich Ann said:
> Is it a 'norm' that DBA's use a data model (both logical and physical)
> to design a database?
Theoretically, yes.
> or
> Are data models mostly used as a 'post-design' afterthought, to document
> what is physically already implemented?
If you're lucky. Most of the time they are out-of-date, half-assed
attempts at documentation or design models that were given up on.
> In most shops that I have worked, we never used a model. And if they did
> have a data model, it was never up-to-date.
> The model was an after-thought for documentation purposes... It was done
> after the table was implemented in Production.
Yes.
> Is there a good modeling tool for Informix 10?
I don't like Erwin any more. Had too many problems with it.
> Is there any one 'data modeling' design class that is better then
> others?
I only ever attended the Informix RDBD course, it was good enough for me. ;o)
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Reply » Rate this post: Text for clearing space
From: Sebastian, Norma J. - view profile
Date: Fri, Dec 8 2006 1:44 am
Email: "Sebastian, Norma J."
Yes, Rational Data Architect should support Informix.
At the Chicago IIUG meeting yesterday there was a demo on it.
Quite impressive and worth checking out.
If you have an IBM rep, ask him/her about it.
Norma Jean Sebastian
ERP Support Administration
GIS- Enterprise Technical Services
- Hide quoted text -
- Show quoted text -
-----Original Message-----
From: informix-list-boun...@iiug.org
[mailto:informix-list-boun...@iiug.org] On Behalf Of Ian Michael Gumby
Sent: Thursday, December 07, 2006 2:53 PM
To: Uri...@mcao.maricopa.gov; informix-l...@iiug.org
Subject: RE: Data Modeling?
>Is it a 'norm' that DBA's use a data model (both logical and physical)
>to design a database?
>or
>Are data models mostly used as a 'post-design' afterthought, to
document
>what is physically already implemented?
>In most shops that I have worked, we never used a model. And if they
did
>have a data model, it was never up-to-date.
>The model was an after-thought for documentation purposes... It was
done
>after the table was implemented in Production.
>Is there a good modeling tool for Informix 10?
>Is there any one 'data modeling' design class that is better then
>others?
Now this is a loaded question...
Separate the logical from the physical.
A physical model helps the DBAs plan out how to lay out the data to get
the
most bang for the buck.
The logical model helps when you need to determine the relationship
between
tables, constraints , etc... The logical model helps those who grok
database
design. Some shops swear by using models.
Others ignore them and treat the database as a persistence of objects.
IMHO, you'd be a fool not use a modeling tool. A good tool will shave
time
off of development.
With respect to which tool, I've liked Erwin in the past, although the
3K
pricetag and I'm not sure if the latest release supports IDS makes it
less
than desirable. (I support multiple platforms and RDBMSs)
I've downloaded a copy of the Rational Data Architect trial version.
(Actually Its going on in another window right now.) Its an eclipse plug
in,
and I've noticed that you can click on DB2 support.
Didn't see IDS there.
Will let you know what I think of it. ...
_________________________________________________________________
Stay up-to-date with your friends through the Windows Live Spaces
friends
list.
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=htt...
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
Reply » Rate this post: Text for clearing space
Subject changed: Pls help to tune my informix engine .. thanks
From: Sebastian, Norma J. - view profile
Date: Fri, Dec 8 2006 1:42 am
Email: "Sebastian, Norma J."
Darren,
Maybe it's a rootdbs problem ;)
Norma Jean Sebastian
ERP Support Administration
GIS- Enterprise Technical Services
- Hide quoted text -
- Show quoted text -
-----Original Message-----
From: informix-list-boun...@iiug.org
[mailto:informix-list-boun...@iiug.org] On Behalf Of
Darren_Jac...@carmax.com
Sent: Thursday, December 07, 2006 12:52 PM
To: Obnoxio The Clown
Cc: informix-l...@iiug.org
Subject: Re: Pls help to tune my informix engine .. thanks
Ha Ha Ha....Guess I'm not immune.
Kinda hard to perf tune without being on the box.
BTW, have your tried update statistics! 8-P
peace
"Obnoxio The
Clown"
ita.com> Darren_Jac...@carmax.com
Sent by:
cc
informix-list-bou informix-l...@iiug.org
n...@iiug.org
Subject
Re: Pls help to tune my informix
engine .. thanks
12/07/2006 01:36
PM
Darren_Jac...@carmax.com said:
> And yes, have you tried update statistics?
Cut the guy some slack. Not everyone is perfect like you.
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly
to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
Reply » Rate this post: Text for clearing space
From: Henry - view profile
Date: Fri, Dec 8 2006 2:22 am
Email: "Henry"
Hi gurus,
Here are the answers for the questions:
1) What type of system - DSS or OLTP?
- DSS
2) Result of "top"
- i think during peak hours, server is still around 30% IDLE.
3) What are your checkpoints?
- CKPTINTVL 300
4) What's your virtsize?
- is it this? :) => SHMVIRTSIZE 500000
5) What is the physical size of mem on the box?
- physical mem
Physical: 4194280 Kbytes, lockable: 3768188 Kbytes, available: 3633152
Kbytes
- sawpinfo
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 2097152 0 2097152 0% 0 - 1
/dev/vg00/lvol2
dev 2674688 0 2674688 0% 0 - 1
/dev/vgclar/swap6
reserve - 1898540 -1898540
memory 3159584 707668 2451916 22%
6) What type of storage?
- currently: EMC Clariion, will move to NetApp soon
7) Do you have a hot disk?
- not sure... may i know how to check?
8) Raw or cooked?
- raw
9) onstat -m
15:37:44 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
buffers not flushed.
15:37:44 Checkpoint loguniq 7127, logpos 0x472d4, timestamp:
0x2a86ca65
15:37:44 Maximum server connections 133
15:42:48 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
buffers not flushed.
15:42:48 Checkpoint loguniq 7127, logpos 0x4816c, timestamp:
0x2a873943
15:42:48 Maximum server connections 133
15:47:51 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
buffers not flushed.
15:47:51 Checkpoint loguniq 7127, logpos 0x69724, timestamp:
0x2a8c4328
15:47:51 Maximum server connections 133
15:52:53 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
buffers not flushed.
15:52:53 Checkpoint loguniq 7127, logpos 0x6b744, timestamp:
0x2a8cc3e5
15:52:53 Maximum server connections 133
15:57:56 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
buffers not flushed.
15:57:56 Checkpoint loguniq 7127, logpos 0x6c358, timestamp:
0x2a8d1b94
10) onstat -g seg
Segment Summary:
id key addr size ovhd class
blkused blkfree
7 1381451777 c000000000659000 1128763392 458304 R
275552 25
8 1381451778 c000000043ad2000 512000000 16296 V
35986 89014
Total: - - 1640763392 - -
311538 89039
11) we are performing update statistics on those widely used tables
everyday
12) KAIO?? not sure ... how to check whether we are using it or not?
Thanks a lot for your help. :)
Reply » Rate this post: Text for clearing space
Subject changed: Data Modeling?
From: Dirk B. - view profile
Date: Fri, Dec 8 2006 3:55 am
Email: "Dirk B."
Urich Ann schrieb:
- Hide quoted text -
- Show quoted text -
> Is it a 'norm' that DBA's use a data model (both logical and physical)
> to design a database?
> or
> Are data models mostly used as a 'post-design' afterthought, to document
> what is physically already implemented?
> In most shops that I have worked, we never used a model. And if they did
> have a data model, it was never up-to-date.
> The model was an after-thought for documentation purposes... It was done
> after the table was implemented in Production.
> Is there a good modeling tool for Informix 10?
> Is there any one 'data modeling' design class that is better then
> others?
Sybase Powerdesigner supports IDS too. You can get a 15 day trial at
their website, you-ll probably just need the data-architect.
It-s powerful, very customizable and works reliable with IDS.
Regards
Dirk
Reply » Rate this post: Text for clearing space
Subject changed: Pls help to tune my informix engine .. thanks
From: TBP - view profile
Date: Fri, Dec 8 2006 10:15 am
Email: TBP
- Hide quoted text -
- Show quoted text -
Henry wrote:
> Hi gurus,
> Here are the answers for the questions:
> 1) What type of system - DSS or OLTP?
> - DSS
> 2) Result of "top"
> - i think during peak hours, server is still around 30% IDLE.
> 3) What are your checkpoints?
> - CKPTINTVL 300
> 4) What's your virtsize?
> - is it this? :) => SHMVIRTSIZE 500000
> 5) What is the physical size of mem on the box?
> - physical mem
> Physical: 4194280 Kbytes, lockable: 3768188 Kbytes, available: 3633152
> Kbytes
> - sawpinfo
> Kb Kb Kb PCT START/ Kb
> TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
> dev 2097152 0 2097152 0% 0 - 1
> /dev/vg00/lvol2
> dev 2674688 0 2674688 0% 0 - 1
> /dev/vgclar/swap6
> reserve - 1898540 -1898540
> memory 3159584 707668 2451916 22%
> 6) What type of storage?
> - currently: EMC Clariion, will move to NetApp soon
> 7) Do you have a hot disk?
> - not sure... may i know how to check?
> 8) Raw or cooked?
> - raw
> 9) onstat -m
> 15:37:44 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
> buffers not flushed.
> 15:37:44 Checkpoint loguniq 7127, logpos 0x472d4, timestamp:
> 0x2a86ca65
> 15:37:44 Maximum server connections 133
> 15:42:48 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
> buffers not flushed.
> 15:42:48 Checkpoint loguniq 7127, logpos 0x4816c, timestamp:
> 0x2a873943
> 15:42:48 Maximum server connections 133
> 15:47:51 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
> buffers not flushed.
> 15:47:51 Checkpoint loguniq 7127, logpos 0x69724, timestamp:
> 0x2a8c4328
> 15:47:51 Maximum server connections 133
> 15:52:53 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
> buffers not flushed.
> 15:52:53 Checkpoint loguniq 7127, logpos 0x6b744, timestamp:
> 0x2a8cc3e5
> 15:52:53 Maximum server connections 133
> 15:57:56 Fuzzy Checkpoint Completed: duration was 0 seconds, 3
> buffers not flushed.
> 15:57:56 Checkpoint loguniq 7127, logpos 0x6c358, timestamp:
> 0x2a8d1b94
> 10) onstat -g seg
> Segment Summary:
> id key addr size ovhd class
> blkused blkfree
> 7 1381451777 c000000000659000 1128763392 458304 R
> 275552 25
> 8 1381451778 c000000043ad2000 512000000 16296 V
> 35986 89014
> Total: - - 1640763392 - -
> 311538 89039
> 11) we are performing update statistics on those widely used tables
> everyday
> 12) KAIO?? not sure ... how to check whether we are using it or not?
> Thanks a lot for your help. :)
Well, "being on the box" (nice phrase) would help :P
Still ...
NOAGE 1
RESIDENT -1
NUMAIOVPS 31 (assuming no kaio 'cos no RESIDENT)
CLEANERS 31
Change your RA_PAGES / RA_THRESHOLD to 64 / 8
What about temp dbspaces ... do you have any? Make sure you have at least 3.
Are you using PDQ? If so the 6 for DS_MAX_QUERIES seems possibly low.
and yes more BUFFERS.
Check for kaio with onstat -g ath and see if there are kaio threads. You
may get more out using KAIO.
Difficult to make any definite statements as no info on how long the
engine has been up.
Reply » Rate this post: Text for clearing space
From: Superboer - view profile
Date: Fri, Dec 8 2006 10:44 am
Email: "Superboer"
> and yes more BUFFERS.
you can add buffers until the cows come in
if you have set LRUMAX/MINDIRTY to 70/60 %
then you will have a read cache of
30/40 % of 500.000 pages.
The engine will not flush a dirty buffer for a page which needs to be
in memory for reading....
(sometimes it does and that is called a foreground write which we all
want to avoid.....)
So if readcache should be improved, then reduce LRUMAX/MINDIRTY to
say 10 / 5 %
Superboer.
Reply » Rate this post: Text for clearing space
Subject changed: Data Modeling?
From: Clive Eisen - view profile
Date: Fri, Dec 8 2006 2:06 pm
Email: Clive Eisen
Urich Ann wrote:
> Is it a 'norm' that DBA's use a data model (both logical and physical)
> to design a database?
if you have time (:-))
> Is there a good modeling tool for Informix 10?
http://www.dbschema.com/
Low cost - $45.00 - and pretty good
Supports Informix as well as all the usual suspects
Will import a schema as well as allowing design from scratch
--
Clive
Reply » Rate this post: Text for clearing space
From: Double Echo - view profile
Date: Fri, Dec 8 2006 3:36 pm
Email: Double Echo
- Hide quoted text -
- Show quoted text -
Urich Ann wrote:
> Is it a 'norm' that DBA's use a data model (both logical and physical)
> to design a database?
> or
> Are data models mostly used as a 'post-design' afterthought, to document
> what is physically already implemented?
> In most shops that I have worked, we never used a model. And if they did
> have a data model, it was never up-to-date.
> The model was an after-thought for documentation purposes... It was done
> after the table was implemented in Production.
> Is there a good modeling tool for Informix 10?
> Is there any one 'data modeling' design class that is better then
> others?
I think you should look at Data Modeling from two perspectives. One view
is for new work, the second for existing systems. On new work, I think
the best Data Modeling tool is still pencil and paper, or a white board.
This is a fun time, where you can actually design the data without any
restrictions other than what the business people throw into it to screw
it up.
On existing systems, probably best to reverse engineer the schema with
some kind of software tool, but even the best I've never used to go and
make changes to the database with it. PowerDesigner does a good job,
even Visio is adequate for that. But I've yet to see some kind of software
product actually go beyond just linking primary and foreign keys. You can
write your own column cross-reference tool to see column relationships,
which is basically what visual tools are going to do, unless you can link
some kind of documentation in the metadata describing what each table is
and how it relates to the other tables. Good luck if you find something
that doesn't cost a fortune, and actually does more than just draw lines
connecting primary-keys and foreign-keys together. The evaluation of
current products will be interesting, you should produce a white paper
of comparison products, it might be quite beneficial for the rest of
us.
Reply » Rate this post: Text for clearing space
From: Mike Badar - view profile
Date: Fri, Dec 8 2006 4:27 pm
Email: "Mike Badar"
DE,
On new
> work, I think
> the best Data Modeling tool is still pencil and paper, or a
> white board.
I completly agree.
Mike
- Hide quoted text -
- Show quoted text -
> This is a fun time, where you can actually design the data without any
> restrictions other than what the business people throw into
> it to screw
> it up.
> On existing systems, probably best to reverse engineer the
> schema with
> some kind of software tool, but even the best I've never used
> to go and
> make changes to the database with it. PowerDesigner does a good job,
> even Visio is adequate for that. But I've yet to see some
> kind of software
> product actually go beyond just linking primary and foreign
> keys. You can
> write your own column cross-reference tool to see column
> relationships,
> which is basically what visual tools are going to do, unless
> you can link
> some kind of documentation in the metadata describing what
> each table is
> and how it relates to the other tables. Good luck if you
> find something
> that doesn't cost a fortune, and actually does more than just
> draw lines
> connecting primary-keys and foreign-keys together. The evaluation of
> current products will be interesting, you should produce a white paper
> of comparison products, it might be quite beneficial for the rest of
> us.
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Sebastian, Norma J. - view profile
Date: Fri, Dec 8 2006 4:58 pm
Email: "Sebastian, Norma J."
I totally agree with the pencil and paper route.
One comment on tools doing more than just the basics.....checkout IBM's
Rational Data Architect. I saw a demo and it's cool. Seriously......
for example, reverse engineer and existing database, pull the physical
layout (DBA) into a logical layout (non-DBA), modify either logical or
physical... and then you can compare the 2.... like doing a "diff" on
unix files. Then the comparison will allow you to push changes one way
or the other, or generate script to do it when you want/incorporate into
whatever else you got going on.
Also the "diff" functionality is useful for comparing the physical
layout stored in the tool with the actual DB layout on the server....
and thereby allowing you to generate alter statements to adjust the DB
on the server (you know the DB we as DBAs actually work with ;)).....
So you can keep the managers and other people who care about tracking
data models and stuff happy, and still get your job done ;)
Don't know the cost....
Lord help me, I've been drinking the blue Kool-aid..... better blue than
red ;p
Norma Jean Sebastian
ERP Support Administration
GIS- Enterprise Technical Services
- Hide quoted text -
- Show quoted text -
-----Original Message-----
From: informix-list-boun...@iiug.org
[mailto:informix-list-boun...@iiug.org] On Behalf Of Double Echo
Sent: Friday, December 08, 2006 7:36 AM
To: informix-l...@iiug.org
Subject: Re: Data Modeling?
Urich Ann wrote:
> Is it a 'norm' that DBA's use a data model (both logical and physical)
> to design a database?
> or
> Are data models mostly used as a 'post-design' afterthought, to
document
> what is physically already implemented?
> In most shops that I have worked, we never used a model. And if they
did
> have a data model, it was never up-to-date.
> The model was an after-thought for documentation purposes... It was
done
> after the table was implemented in Production.
> Is there a good modeling tool for Informix 10?
> Is there any one 'data modeling' design class that is better then
> others?
I think you should look at Data Modeling from two perspectives. One view
is for new work, the second for existing systems. On new work, I think
the best Data Modeling tool is still pencil and paper, or a white board.
This is a fun time, where you can actually design the data without any
restrictions other than what the business people throw into it to screw
it up.
On existing systems, probably best to reverse engineer the schema with
some kind of software tool, but even the best I've never used to go and
make changes to the database with it. PowerDesigner does a good job,
even Visio is adequate for that. But I've yet to see some kind of
software
product actually go beyond just linking primary and foreign keys. You
can
write your own column cross-reference tool to see column relationships,
which is basically what visual tools are going to do, unless you can
link
some kind of documentation in the metadata describing what each table is
and how it relates to the other tables. Good luck if you find something
that doesn't cost a fortune, and actually does more than just draw lines
connecting primary-keys and foreign-keys together. The evaluation of
current products will be interesting, you should produce a white paper
of comparison products, it might be quite beneficial for the rest of
us.
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
Reply » Rate this post: Text for clearing space
From: Urich Ann - view profile
Date: Fri, Dec 8 2006 7:19 pm
Email: "Urich Ann"
Thanks to ALL, for the feedback and suggestions.
If I am able to test many/most of the data modeling tools, I will write
a white paper on the results.
And, I'll add to the white paper, whether a 'formal' data modeling class
teaches me anything new...
... or if it is about what I ALREADY know and am using -- but is
'labeling' it differently.
:)
Thanks.
Ann
- Hide quoted text -
- Show quoted text -
-----Original Message-----
From: informix-list-boun...@iiug.org
[mailto:informix-list-boun...@iiug.org] On Behalf Of Double Echo
Sent: Friday, December 08, 2006 6:36 AM
To: informix-l...@iiug.org
Subject: Re: Data Modeling?
I think you should look at Data Modeling from two perspectives. One view
is for new work, the second for existing systems. On new work, I think
the best Data Modeling tool is still pencil and paper, or a white board.
This is a fun time, where you can actually design the data without any
restrictions other than what the business people throw into it to screw
it up.
On existing systems, probably best to reverse engineer the schema with
some kind of software tool, but even the best I've never used to go and
make changes to the database with it. PowerDesigner does a good job,
even Visio is adequate for that. But I've yet to see some kind of
software product actually go beyond just linking primary and foreign
keys. You can write your own column cross-reference tool to see column
relationships, which is basically what visual tools are going to do,
unless you can link some kind of documentation in the metadata
describing what each table is and how it relates to the other tables.
Good luck if you find something that doesn't cost a fortune, and
actually does more than just draw lines connecting primary-keys and
foreign-keys together. The evaluation of current products will be
interesting, you should produce a white paper of comparison products, it
might be quite beneficial for the rest of us.
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Ian Michael Gumby - view profile
Date: Fri, Dec 8 2006 8:40 pm
Email: "Ian Michael Gumby"
>I totally agree with the pencil and paper route.
But with pencil and paper you then have to write the DDL and you lack the
ability to reverse engineer it. Also you have to store it somewhere and its
not always available....
- Hide quoted text -
- Show quoted text -
>One comment on tools doing more than just the basics.....checkout IBM's
>Rational Data Architect. I saw a demo and it's cool. Seriously......
>for example, reverse engineer and existing database, pull the physical
>layout (DBA) into a logical layout (non-DBA), modify either logical or
>physical... and then you can compare the 2.... like doing a "diff" on
>unix files. Then the comparison will allow you to push changes one way
>or the other, or generate script to do it when you want/incorporate into
>whatever else you got going on.
>Also the "diff" functionality is useful for comparing the physical
>layout stored in the tool with the actual DB layout on the server....
>and thereby allowing you to generate alter statements to adjust the DB
>on the server (you know the DB we as DBAs actually work with ;)).....
>So you can keep the managers and other people who care about tracking
>data models and stuff happy, and still get your job done ;)
>Don't know the cost....
>Lord help me, I've been drinking the blue Kool-aid..... better blue than
>red ;p
Thats like saying Vodka is better than Gin, or that small batch burbons are
better than single malts.
(Its all what your palet prefers. ;-)
- Hide quoted text -
- Show quoted text -
>Norma Jean Sebastian
>ERP Support Administration
>GIS- Enterprise Technical Services
>-----Original Message-----
>From: informix-list-boun...@iiug.org
>[mailto:informix-list-boun...@iiug.org] On Behalf Of Double Echo
>Sent: Friday, December 08, 2006 7:36 AM
>To: informix-l...@iiug.org
>Subject: Re: Data Modeling?
>Urich Ann wrote:
> > Is it a 'norm' that DBA's use a data model (both logical and physical)
> > to design a database?
> > or
> > Are data models mostly used as a 'post-design' afterthought, to
>document
> > what is physically already implemented?
> > In most shops that I have worked, we never used a model. And if they
>did
> > have a data model, it was never up-to-date.
> > The model was an after-thought for documentation purposes... It was
>done
> > after the table was implemented in Production.
> > Is there a good modeling tool for Informix 10?
> > Is there any one 'data modeling' design class that is better then
> > others?
>I think you should look at Data Modeling from two perspectives. One view
>is for new work, the second for existing systems. On new work, I think
>the best Data Modeling tool is still pencil and paper, or a white board.
>This is a fun time, where you can actually design the data without any
>restrictions other than what the business people throw into it to screw
>it up.
>On existing systems, probably best to reverse engineer the schema with
>some kind of software tool, but even the best I've never used to go and
>make changes to the database with it. PowerDesigner does a good job,
>even Visio is adequate for that. But I've yet to see some kind of
>software
>product actually go beyond just linking primary and foreign keys. You
>can
>write your own column cross-reference tool to see column relationships,
>which is basically what visual tools are going to do, unless you can
>link
>some kind of documentation in the metadata describing what each table is
>and how it relates to the other tables. Good luck if you find something
>that doesn't cost a fortune, and actually does more than just draw lines
>connecting primary-keys and foreign-keys together. The evaluation of
>current products will be interesting, you should produce a white paper
>of comparison products, it might be quite beneficial for the rest of
>us.
>_______________________________________________
>Informix-list mailing list
>Informix-l...@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list
>============================================================
>The information contained in this message may be privileged
>and confidential and protected from disclosure. If the reader
>of this message is not the intended recipient, or an employee
>or agent responsible for delivering this message to the
>intended recipient, you are hereby notified that any reproduction,
>dissemination or distribution of this communication is strictly
>prohibited. If you have received this communication in error,
>please notify us immediately by replying to the message and
>deleting it from your computer. Thank you. Tellabs
>============================================================
>_______________________________________________
>Informix-list mailing list
>Informix-l...@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
Get the latest Windows Live Messenger 8.1 Beta version. Join now.
http://ideas.live.com
Reply » Rate this post: Text for clearing space
From: Double Echo - view profile
Date: Sat, Dec 9 2006 1:17 am
Email: Double Echo
Ian Michael Gumby wrote:
>> I totally agree with the pencil and paper route.
> But with pencil and paper you then have to write the DDL and you lack
> the ability to reverse engineer it. Also you have to store it somewhere
> and its not always available....
I should have been a bit more clear for folks like you who would nit pick
over what I said. Also note this is for new work, not existing work which
I thought I made clear. Obviously I've failed you.
The pencil and paper phase would not really include DDL unless you were
that ambitious--at least not the way I've been doing it. It's really
more about drawing boxes, and identifying primary keys, and creating new
boxes when you find columns that might have more than one-to-many.
For example, and I'm not going to debate this ad infinitum so please don't
pick it to death:
I am a person with a name.
box 1 -- person table
I own several automobiles.
Well that's a "many", I need a new table.
box 2 -- draw line from person table to new box called cars. Note a
primary key, and draw a line from the person to the cars table.
Keep going, build your straw man data model on paper and white board.
When it comes to the details and more and more, then you can go into
some kind of drawing/data-model software. The discovery process of data
modeling should accomplish two things:
1. basic normalization of your tables that appear at a simple high
level with maybe some details as needed.
2. your business model gets shaken out with glaring bugs
After the initial exploration you can go back and refine it. It is actually
a lot of fun if you do this as a group with a DBA and business people in the
same room, hashing it out. As a DBA you learn about the business, as a business
person you learn about the data. Great exercise for teams on both the business
side and the tech side. Can actually build bridges or contempt depending on
the people, either way it can be fun. :-)
-DE-
Reply » Rate this post: Text for clearing space
End of messages
No comments:
Post a Comment