I had to dig a bit to figure this out, and I know I've done this before so I'm making a note here so I can find this information again the next time I forget about it.
To automate a standard MSI install, run it once and log all the properties to a file:
msiexec /i "My Program Installer.msi" /Lp options.log
Make a note of anything you change during this first run. Then take a look at your options.log file. It should have a lot of items like this:
Property(C): ProgramFilesFolder = C:\Program Files\
Property(C): SourceDir = E:\
Property(C): VersionNT = 501
Property(C): ALLUSERS = 1
Property(C): INSTALLLOCATION = C:\Program Files\Whoever\Whatever
Property(C): Manufacturer = Whoever
You can run a silent install by setting these properties on the command line. The ones in all caps are "public" so they can be set by msiexec:
msiexec /qn /i "My Program Installer.msi" INSTALLLOCATION="C:\MyFolder\Whatever"
That's all there is to it! Find the properties you want, pass them on the command line. I have no idea why I can't seem to remember the steps to do that.
Tuesday, August 24, 2010
Thursday, May 27, 2010
Going Meta
Lately I've been using some of the same tricks over and over again in different contexts. Essentially I've just been doing a lot of complex queries, where I select a set of records first, and then select other data out of the first set:
select my_set.X, my_set.Y, my_set.Z
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
Why would I need to do this? Well, for one thing it breaks a problem down into smaller bits, so it is easier to work on. I can write and validate a query that gets all the records I might want to work with, and then write two or three queries that use it to produce friendlier output. I can also use it to do things like counts:
select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
This will produce something that looks like this:
You can do grand totals using a UNION ALL with another query:
select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
UNION ALL
select 'Totals',
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
The key to doing a UNION is you need your columns to match up. So note that I have the same number of columns (3) and I used the same names (Item, Num_Chicago, Num_Phoenix, Total) to describe them. By not including my_set.X in the second query I end up with total counts of all the rows, not simply of one item. The output from this should look like:
I've used the same sort of complex queries to pull the earliest or latest item by category from a set of records; it is a fairly simple concept but can be very powerful.
select my_set.X, my_set.Y, my_set.Z
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
Why would I need to do this? Well, for one thing it breaks a problem down into smaller bits, so it is easier to work on. I can write and validate a query that gets all the records I might want to work with, and then write two or three queries that use it to produce friendlier output. I can also use it to do things like counts:
select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
This will produce something that looks like this:
| Item | Num_Chicago | Num_Phoenix | Total |
| Item 1 | 4 | 1 | 5 |
| Item 2 | 2 | 2 | 4 |
You can do grand totals using a UNION ALL with another query:
select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
UNION ALL
select 'Totals',
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
The key to doing a UNION is you need your columns to match up. So note that I have the same number of columns (3) and I used the same names (Item, Num_Chicago, Num_Phoenix, Total) to describe them. By not including my_set.X in the second query I end up with total counts of all the rows, not simply of one item. The output from this should look like:
| Item | Num_Chicago | Num_Phoenix | Total |
| Item 1 | 4 | 1 | 5 |
| Item 2 | 2 | 2 | 4 |
| Totals | 6 | 3 | 9 |
I've used the same sort of complex queries to pull the earliest or latest item by category from a set of records; it is a fairly simple concept but can be very powerful.
SQL*Plus formatting and automation
Here is some advice on how to run your SQL and generate a file with the delimiter of your choice.
Once you have tweaked these settings to your liking, you can automate the process pretty easily. Put your commands into a file. To suppress output on screen you can set termout off (this only works with a command file):
set pagesize 5000 linesize 500 colsep ',' feedback off termout off
spool output.txt
select whatever;
spool off
quit
Then run sqlplus with that file as an argument (the -S will run the commands silently):
sqlplus -S login/pwd@server @commands.txt
An added bonus of doing it this way is you won’t see the sql commands in your output file anymore.
This is particularly handy if you need to do some scripting in an environment where you don't have control over what libraries are installed; I recently used something similar on a unix machine that had Perl installed but not DBD::Oracle. I shelled out and ran my query using sqlplus, and parsed the output in the script. It won't perform as well as a built-in, but for occasional use it is very low rent.
- Login to sqlplus
- Set your page size (how often you get headers – so set this to be arbitrarily large), line size (try to make this long enough to hold a complete row of data), column separator, and suppress the “X rows selected” feedback.
set pagesize 5000 linesize 500 colsep ',' feedback off - Setup an output file
spool output.txt - Run your sql command
select ... from ... where ... ; - Turn off the output
spool off
At this point you should have a file called output.txt with your data. Don’t worry if you have some command data in there, just read on. - You might also read up on how to format columns – in particular you can have nicer column headings:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm
Once you have tweaked these settings to your liking, you can automate the process pretty easily. Put your commands into a file. To suppress output on screen you can set termout off (this only works with a command file):
set pagesize 5000 linesize 500 colsep ',' feedback off termout off
spool output.txt
select whatever;
spool off
quit
Then run sqlplus with that file as an argument (the -S will run the commands silently):
sqlplus -S login/pwd@server @commands.txt
An added bonus of doing it this way is you won’t see the sql commands in your output file anymore.
This is particularly handy if you need to do some scripting in an environment where you don't have control over what libraries are installed; I recently used something similar on a unix machine that had Perl installed but not DBD::Oracle. I shelled out and ran my query using sqlplus, and parsed the output in the script. It won't perform as well as a built-in, but for occasional use it is very low rent.
Sunday, April 25, 2010
iPad and DHCP
I started wondering about the iPad and DHCP after noticing in my router's log files that the iPad was pretty chatty; re-associating with the AP really often even when sleeping. Here is about 40 minutes' worth of router logs, and notice the iPad associates with the AP every minute or so (I replaced my MAC address with "iPad" here):
The really scary thing about this is the iPad was asleep, sitting on the charger for all but the last 10 minutes or so of this log. So it appears to be chatting up the network even when you're not using it.
I ran a network sniffer from another machine to capture broadcast traffic on the wireless LAN which backed up what I saw in the router log; I have a DHCP request and acknowledgment about every minute, as well as a bunch of ARP traffic. This is the sequence that was repeated every minute:
Now a normal DHCP request has 4 packets: discover, offer, request, and acknowledgment (or 'ack'). A DHCP renewal just uses the last two: the request and the ack. There's something fishy about this, though:
This is all a mystery to me, but I will say I think there's something odd going on with the network interface on the iPad. The next step is to setup a wireless access point with a sniffer onboard to see if I can see not only the broadcast traffic, but any direct communication between the router and the iPad as well. I welcome comments from people who may know more than I do about DHCP, the iPad, or wireless networking.
[INFO] Sun Apr 25 14:39:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:39:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:38:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:38:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:37:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:37:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:36:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:36:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:35:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:35:50 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:34:50 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:34:50 2010 Wireless system with MAC address "iPad" associated
... more of the same ...
[INFO] Sun Apr 25 14:03:10 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:10 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:03:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:02:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:02:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:01:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:01:53 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:00:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:00:53 2010 Wireless system with MAC address "iPad" associated
The really scary thing about this is the iPad was asleep, sitting on the charger for all but the last 10 minutes or so of this log. So it appears to be chatting up the network even when you're not using it.
I ran a network sniffer from another machine to capture broadcast traffic on the wireless LAN which backed up what I saw in the router log; I have a DHCP request and acknowledgment about every minute, as well as a bunch of ARP traffic. This is the sequence that was repeated every minute:
Time Source Destination Protocol Info
35.434182 0.0.0.0 255.255.255.255 DHCP DHCP Request - Transaction ID "iPad"
35.440707 192.168.0.1 255.255.255.255 DHCP DHCP ACK - Transaction ID "iPad"
35.441587 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
35.442794 "iPad" Broadcast ARP Gratuitous ARP for 192.168.0.109 (Request)
35.533334 "iPad" Broadcast ARP Gratuitous ARP for 0.0.0.0 (Request)
35.793304 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
36.194540 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
36.444386 "iPad" Broadcast ARP Who has 192.168.0.1? Tell 192.168.0.109
36.595654 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
41.468674 "iPad" Broadcast ARP Who has 192.168.0.2? Tell 192.168.0.109
Now a normal DHCP request has 4 packets: discover, offer, request, and acknowledgment (or 'ack'). A DHCP renewal just uses the last two: the request and the ack. There's something fishy about this, though:
- For a DHCP renewal it is common to send my request packet to the DHCP server I've been using; that is, the first packet should be to 192.168.0.1, not to the broadcast address 255.255.255.255.
- One interesting item in the ARP requests is that the iPad looks for address 169.254.255.255, which is reserved as an "autoconfiguration" address for devices that cannot be configured using DHCP. Why would it be looking for that address if it had processed the DHCP acknowledgment?
- I think the iPad must have seen the DHCP acknowledgment because it looks for the DNS servers, 192.168.0.1 and 192.168.0.2, listed in the DHCP ack. How else would it be interested in those specific IP addresses?
- This could be a WiFi association problem instead of strictly DHCP-related; if it were aware that it was still on my network it wouldn't need to send a DHCP renewal for 12 hours, since my lease times are set up for 24. Why is it re-associating every minute and sending a fresh DHCP request? Is it because it doesn't know it has a lease, or because it doesn't know what network it is attached to? I think it could be either.
- If the machine associates to a new wireless access point (or doesn't realize it is on the same network), there's a good reason for it to send out a DHCP request packet (though honestly I think it should be starting over at 'discover' if it doesn't know where it is) - it could be that sending the request packet to the broadcast address was an attempt to workaround some problem with renewals on the same network versus requests on a new network.
This is all a mystery to me, but I will say I think there's something odd going on with the network interface on the iPad. The next step is to setup a wireless access point with a sniffer onboard to see if I can see not only the broadcast traffic, but any direct communication between the router and the iPad as well. I welcome comments from people who may know more than I do about DHCP, the iPad, or wireless networking.
Tuesday, December 15, 2009
Reinstalling Windows on a netbook
A friend of mine and I both bought HP mini netbooks when they came out last year. As it turns out, we were both ready to reload them and pass them along to other people this year. I spent some time figuring out how to do this, and wrote up instructions for my friend. They worked for him, so maybe they can also help you. Use these instructions at your own risk.
Some things to know before you get started:
1. The first thing you'll need to do is prepare an SD card with the Windows install and the contents of your HP driver disc. I did that using a lot of help and tools I found here: http://www.liliputing.com/2008/04/install-windows-xp-on-mini-note-usb.html - I used a 4GB flash card so I'd have room to copy the HP disc onto the same card, which ended up working well. You don't need a special process with the HP disc, just copy the files into a folder on the card.
2. You need the CD-Key that is on the sticker on the bottom of your laptop (check to see you haven't worn it off somehow, nice placement HP) and you'll need to activate windows once it is installed (it went smoothly for me, and I did it twice).
Here is the procedure as I explained it to my friend:
1. You might want to make sure the little tab on the SD card is set to 'Lock' - slide it down away from the business end of the card. I set it that way and I think it is safest, that should make it read-only.
2. Insert SD card. It is probably best to remove all the other flash drives, peripherals, etc. from the machine to simplify things.
3. Boot, hit F9 to choose boot device, choose the flash card (should say USB I think)
4. You'll get a standard XP boot prompt - choose the text only install (should be labeled '1.') to start (you'll switch to GUI after reboot)
5. Follow windows setup prompts:
a. Even if you already have a C:\ drive, go ahead and delete the partition on your internal disk and start again. Format it NTFS Quick.
b. If for some reason the SD card comes up as drive C:\, try to change it so your internal disk gets to be C:\ - I don't think this will happen to you since you already have windows on the machine but it happened to me when I switched from Linux back to Windows.
6. Keep watch while windows formats the disk and copies the appropriate files (approx. 10 minutes or so), so you can be ready when the system reboots.
7. On reboot, hit F9 to choose the boot device again!
8. You'll get a standard XP boot prompt - choose the GUI install option this time.
9. This should be the standard XP setup you're used to. Sadly the USB isn't really any faster than a CD, but you don't need to babysit it.
a. Type in your CD Key from the sticker on the bottom of your machine, crap, no mouse! use a combination of tab, enter, 'N' key to move on.
b. It will wait for you to type in the CD key, and your first reboot after the install will probably fail, so its ok to wander off while this runs.
c. For some reason mine took FOREVER at the "Removing any temporary files used... 1 minute remaining" part. It was a LOT more than 1 minute.
10. Once windows has installed, the system will try to reboot but you will get an error:
Hit enter and the system should try to reboot. Hit F9 and boot from the SD card again. Choose the GUI install option again. This should actually boot you into windows. I have no idea why this works, haha.
11. You should be presented a login screen with two users: Owner and UserXP. I've been using Owner and it seems to work. Click Owner to login.
12. Windows will gripe a bit about the display being low-res. Don't worry about it; the HP driver install will fix all of that.
13. Start->My Computer->C:\->show contents. You should have a boot.ini file in C:\ - you might need to go to Tools->Folder options and change some of the settings to see it, but probably not. double-click boot.ini to edit the file. You shouldn't need to change much. I made mine look like this:
14. Remove the UserXP user account from the control panel. Start, Control Panel, User accounts, click on UserXP, click 'Delete this account', don't bother saving files or anything.
15. Go to the SD card (mine was D:\ - the label is USB_XP_710), open the HP folder, open SWSETUP, open APPINSTL, double-click SETUP.EXE
a. Open the tree and uncheck boxes for things you don't want to install - for instance, I don't have bluetooth or a WWAN card so I unchecked these underneath "Hardware Enabling Drivers," and I didn't want instant messenger so I unchecked it under "Recommended Software Applications"
b. In particular, don't bother installing Java. It doesn't seem to work consistently. Just uncheck it and install it from the web later if you need it.
c. Click 'Install' and walk away for an hour or so. It will pop up some boxes that look like you need to do something, but you don't. Just wait the process out. You'll be prompted to reboot at the end.
16. Reboot. Don't hit F9 this time and see if the system can boot on its own. If it can, you're ready to run windows update, activate windows, etc. If it can't try booting from the SD card again and see if you can figure out what has happened. The boot ini needs to point to the internal hard drive (usually disk 0, partition 1 but there might be a reason it is something else).
I hope this helps you. Use these instructions at your own risk.
Some things to know before you get started:
1. The first thing you'll need to do is prepare an SD card with the Windows install and the contents of your HP driver disc. I did that using a lot of help and tools I found here: http://www.liliputing.com/2008/04/install-windows-xp-on-mini-note-usb.html - I used a 4GB flash card so I'd have room to copy the HP disc onto the same card, which ended up working well. You don't need a special process with the HP disc, just copy the files into a folder on the card.
2. You need the CD-Key that is on the sticker on the bottom of your laptop (check to see you haven't worn it off somehow, nice placement HP) and you'll need to activate windows once it is installed (it went smoothly for me, and I did it twice).
Here is the procedure as I explained it to my friend:
1. You might want to make sure the little tab on the SD card is set to 'Lock' - slide it down away from the business end of the card. I set it that way and I think it is safest, that should make it read-only.
2. Insert SD card. It is probably best to remove all the other flash drives, peripherals, etc. from the machine to simplify things.
3. Boot, hit F9 to choose boot device, choose the flash card (should say USB I think)
4. You'll get a standard XP boot prompt - choose the text only install (should be labeled '1.') to start (you'll switch to GUI after reboot)
5. Follow windows setup prompts:
7. On reboot, hit F9 to choose the boot device again!
8. You'll get a standard XP boot prompt - choose the GUI install option this time.
9. This should be the standard XP setup you're used to. Sadly the USB isn't really any faster than a CD, but you don't need to babysit it.
"Windows could not start because the following file is missing or corrupt:\system32\hal.dll. Please re-install a copy of the above file."
Hit enter and the system should try to reboot. Hit F9 and boot from the SD card again. Choose the GUI install option again. This should actually boot you into windows. I have no idea why this works, haha.
11. You should be presented a login screen with two users: Owner and UserXP. I've been using Owner and it seems to work. Click Owner to login.
12. Windows will gripe a bit about the display being low-res. Don't worry about it; the HP driver install will fix all of that.
13. Start->My Computer->C:\->show contents. You should have a boot.ini file in C:\ - you might need to go to Tools->Folder options and change some of the settings to see it, but probably not. double-click boot.ini to edit the file. You shouldn't need to change much. I made mine look like this:
[Boot Loader]
timeout=1
Default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[Operating Systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Home Edition" /noexecute=optin /fastdetect
14. Remove the UserXP user account from the control panel. Start, Control Panel, User accounts, click on UserXP, click 'Delete this account', don't bother saving files or anything.
15. Go to the SD card (mine was D:\ - the label is USB_XP_710), open the HP folder, open SWSETUP, open APPINSTL, double-click SETUP.EXE
I hope this helps you. Use these instructions at your own risk.
Monday, December 7, 2009
Database Tracing in Oracle
You can trace statements in Oracle using a package called "dbms_monitor" - it allows you to see statements that are executed along with some indication of how efficient they are (sort of like using an explain plan, but after the fact). Why would you want to do this? Well, if you have an application that is misbehaving or if you want to know more about how your statements are running live (as opposed to using the explain plan) this can help. Also, if you have a closed package and want to know more about what it is doing in the database this might help. You'll need to be a DBA or be friendly with your DBA to do this, as it requires some privileges and logs are written to the database servers' filesystem.
- Get access to the dbms_monitor package. Your DBA can grant you rights. I do not know the details on this step, I'm afraid.
- Do a select to find out what sessions are active:
SELECT sid, serial#, client_identifier, service_name, action, module, username
FROM V$SESSION
I'd actually use a "WHERE username='my_username'" clause of some kind here to limit what I get, but YMMV. You'll end up with rows that look something like this:
SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION MODULE USERNAME
266 24923 My_Instance SQL Window SQL Devel my_username - Enable logging:
- To log by user session, you need to make a note of the SID and SERIAL# columns, and run this to begin tracing:
begin
dbms_monitor.SESSION_TRACE_ENABLE(266, 24923, TRUE, TRUE);
end; - To log by service and module, you can use those columns:
begin
dbms_monitor.serv_mod_act_trace_enable('My_Instance', 'SQL Devel',
DBMS_MONITOR.ALL_ACTIONS, TRUE, TRUE, NULL);
end;
An upside to doing tracing by service is you can verify it is running:
select * from DBA_ENABLED_TRACES
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME
SERVICE_MODULE My_Instance SQL Devel TRUE TRUE
- To log by user session, you need to make a note of the SID and SERIAL# columns, and run this to begin tracing:
- At some point you will have had enough logging, and you'll want to turn it off.
- If you are tracing by user/session:
begin
dbms_monitor.SESSION_TRACE_DISABLE(266, 24923);
end; - If you are tracing by service/module:
begin
dbms_monitor.serv_mod_act_trace_disable ('My_Instance', 'SQL Devel');
end;
note: with service level tracing you can select * from DBA_ENABLED_TRACES to verify tracing has been turned off, just as you did to determine it was on.
- If you are tracing by user/session:
- So you've traced some information. Where is it? In a file. Where is the file?
select value from v$parameter where name = 'user_dump_dest'
VALUE
/usr/local/oracle/10204/admin/My_Instance/udump - Once you have the file (ask your DBA for the contents of that folder, or better yet the files from today, or this hour or something) you might wonder what is inside. Well, it is only semi-readable at this point. You need to use a utility called TKPROF to reformat the trace into something you can really read. I found I already had a copy of TKPROF in my oracle bin directory:
"C:\oracle\product\10.2.0\client_1\BIN\tkprof.exe" c:\my_instance_ora_23486.trc c:\my_oracle.log
At this point, c:\my_oracle.log should contain a list of SQL statements and performance data for each statement for as long as you had tracing on. - For more information, check out Oracle's site:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm
Thursday, October 29, 2009
tracing in ASP.NET
Just a handy code snippet to add to your code if you've enabled tracing in your web.config:
Subscribe to:
Posts (Atom)