Other Various How-tos/Tasks in ISD

This page is a grab-bag of random tasks that must be done on an occasional basis in the ISD-sybase environment. They didn't necessarily fit into any of the other categories of documentation here.

Questions Answered:
How do i Stop the Sybase SQL Server
how do i stop/start the IC engines
How do i restart my Sybase SQL server?
How do i add a login to the Sybase Server?
What do i need to do if i add a New SQL Server?
How do I change a user's password?
What do i need to do if i Change an sa password?
The log is full on my database...what do i do?

List of misc periodic tasks Sybase DBA should do

Q: How do i stop the Sybase SQL server?
A:

First, if its in the middle of the day, you need to notify the users of the system (especially if its a production system). See the application contacts page to determine whom to notify.
If you're stopping prod11, see the procedures below for stopping the Integration Channel jobs BEFORE you shutdown prod11.
Log into the file server as user sybase (either telnet in or su - sybase from a Unix prompt). You must be user sybase to properly restart the server.
Log into the SQL server as user sa and execute:
1> sp_who
2>go
to see if there are any user jobs still running. Spids 2-7 in version 11 servers are system jobs, and you'll be logged in as sa performing a "select." Any other jobs existant will be lost upon a shutdown and restart.
If you're satisfied the server is ready to shutdown, execute:
1> shutdown
2>go
If this command works as it should, you should receive an message from isql like this:
```
Server SHUTDOWN by request.
The SQL Server is terminating this process.
DB-LIBRARY error:
        Unexpected EOF from SQL Server.    
```
as it dumps you to the Unix prompt.
If the above command does NOT immediately dump you out of isql, you'll receive repeating messages on the screen like this:
```
shutdown waiting for 1 process(es) to complete...
```
Sometimes you'll be shutting down a server in an emergency situation and won't have the chance to notify users or safely kill all server jobs. Other times you'll be shutting down specifically to kill a job that won't die by itself. In cases like this, you'll need to issue this command: (by the way, if you get to this step b/c your normal shutdown request didn't work, you'll have to open a new window with a new isql session...):
1> shutdown with nowait
2>go
This is not as "nice" a way to shutdown the server; Sybase normally attempts to checkpoint each database and do some generic cleanup when getting shut down, and a nowait shutdown doens't allow Sybase to do this.
In some extreme cases even a shutdown with nowait won't work. In these cases you'll have to attempt to kill the jobs from Unix command line. Execute these commands: (% represents the Unix command line for the sybase unix user)
```
% ps -fe | grep dataserver
  sybase  9751  9750 80 14:33:04 pts/32   0:18 /export/sybase11/bin/dataserver 
-d/dev/rdsk/c0t2d0s5 -sprod -e/export/sybase11/    
```
The second field (in this example, 9751) is the Unix process id of the Sybase process. If you see multiple lines returned from this "ps" command, either you have muliple engines or you have muliple SQL servers on your machine. Don't kill the wrong SQL server! If you have multiple engines, the primary dataserver engine will look like the above example, whereas the subservient engines will have the keyword "ONLINE" in the process listing. As the sybase user, issue this command:
```
% kill -15 9751 
```
then reissue the above ps -fe command to see if the process has died. If the -15 option didn't kill the process, use the next resort:
```
% kill -9 9751 
```
kill -9 is the ultimate of Unix kill signals; it requests immediate death of the process id in question. Its also the most unrecommended way to stop a Sybase server, because it allows absolutely no Sybase preventative pre-shutdown maintenance to occur.
In EXTREME cases, even a sybase kill -9 won't stop the server. Odds are in these cases there is some sort of odd Unix resource problem that can only be cleared by having the root user issue the kill -9 command or by rebooting the entire file server.

Q: How do i stop/start the Integration Channel automatic jobs?
the IC serves as a middle-man process between MIS and the prod11 server. Currently we have two IC engines in place, one updating CDB (Care DataBase) and one updating common with protocol information. Here's what you need to do to shut them down and restart them :
- Log into lis1.cc.nih.gov; this is the host of the IC engines. To properly manipulate these servers you need to be in the ic_mis group and be configured to have access to the /home/ic_mis directory.
- type

% servers

and you'll see something like the following:

Server Key   PID When Started      Last Msg Received OK Ops Bad Ops Errors Rate
---------- ----- ----------------- ----------------- ------ ------- ------ ----
TO_ADT_DB
           95084 1997:8:14:16:57:2 1997:8:19:10:25:4   1993       0      6 NaNQ
TO_PYXIS
           24018 1997:8:14:17:1:31 1997:8:19:10:24:3   1997       6      6 NaNQ
TO_SCC
           31456 1997:8:14:17:1:41 1997:8:19:10:25:1   1993       5      5 NaNQ
ToRepository
           37910 1997:8:11:23:18:2 1997:8:19:11:24:2  19556       0    171 NaNQ

The two Server names we're interested in are "TO_ADT_DB" and "ToRepository."
- To shut them down normally, type

% stops [name]

where name is the server name. If they've lost database connectivity abnormally (as in, prod11 got shutdown or crashed before properly shutting down the IC engines), you'll need to do this before stops:

% clearerr [name]

- Now to start them, simply type:

% starts [name]

and then run the servers monitor to ensure they start and begin to process transactions.

Back to top

Q: Now, how do i restart my Sybase SQL server?
A: As Unix user sybase,

% cd $SYBASE
% cd install
% ls

In the $SYBASE/install directory are files starting with RUN. These are the "RUN" files for sybase servers. Locate the SQL server run file you wish to execute and:

% ./RUN_[servername] &

Make sure you put it in the background; on AIX boxes exiting the shell might shutdown the server. After executing this command, you'll see the Sybase startup messages start to scroll on your screen. You'll see some configuration messages, a Proprietary notice from Sybase, the devices recovering, then the databases recovering. If all goes well, you'll see no error messages and you'll see the Server report "Recovery complete" and an information message describing its default sort order and character sets. Note: until you exit that window in which you started the SQL server, you will receieve all messages generated by the SQL server. Your screen will act as the console, receiving all messages that are appended to the errorlog.

Q: How do i add a user to a database?
A: Well, if the user is a conventional Sybase user, follow the process below. If the user is an HSS/EIS system user, you must follow a security method designed and maintained by Jon Mckeeby and Jim Pitts. See one of them.

1> sp_addlogin [loginname], "initial password", [default database]
2> go
Then, for each database you want them to have access to (you must at least do this for the default database you assigned)
1> use [database]
2> go
1> sp_adduser [loginname]
2> go
Then, you must grant access on objects to the user...sometimes its easier to add the user to a group which already has select or update access on all objects. To add a user to a group, execute this command instead of above
1> sp_adduser [loginname],[loginname],[group_name]
2> go
Furthermore, if the user is going to require all types of access to the database (say, if they're a developer and will be creating objects), then the user must have "dbo" access. Instead of running sp_adduser as above, run this command:
1> sp_addalias [loginname],dbo
2>go

Q: What do i need to do if i add a new server to integrate it into the Operations scheme in ISD?
A: You'll need to do the following if you add a new server:
- In max:/export/syb_ops create a file called .[servername]sapw populated with the sa password. Save the file and chmod 400 it (this makes it readable only by the sybase unix account)
- Edit each of the following scripts and follow the password population conventions for adding the new server. (all scripts in max:/export/syb_ops/)
x nightly/nightly.pl, subroutine populate_sa_pw ()
x weekend/dbcc.sh, main processing loop
x weekend/dr.sh, main processing loop (you'll also need to know the sybase version, the masterdevice name, and the hostname of the machine its on)
x weekend/index.pl, subroutine populate_sa_pw ()
- If you wish to have the server be backed up, you'll have to go through the rigors of creating a new SQL Backtrack control directory. See the SQL Backtrack FAQ section for more details. If your machine doesn't have SQL backtrack but you still want to backup your databases somewhere, you can emulate the backup script on pop:/u/donp/sybase/dumpall.pl.

Back to top

Q: How do i change a users password?
A: Log in as sa, and type:

1> sp_password "[sapassword]", "newpassword", loginname
2> go

If you want to change sa's password, you can just leave off the third field of this statement.

If a user wants to change their own password however, the syntax is a bit different. A user can change their password by doing this:

1> sp_password "oldpassword", "newpassword"
2> go

Note that in all these cases, passwords are displayed on your screen in cleartext. This is an unfortunate security problem, so make sure nobody is looking over your shoulder as you do this (especially if you're logged in as sa). Also, if your passwords have any special characters, the quotes will be necessary or else the server won't read them properly.

Back to top

Q: If i change a sa password, what do i need to do so my operations stuff still works?
A: You'll need to update the /export/syb_ops/.[server]sapw file so that the 4 scripts mentioned above still work. Also, if SQL backtrack backs up your databases your control files are now invalid; you'll have to delete the control files and recreate them w/ the new password. See the FAQ section of the SQL Backtrack page for guidance on this issue.

Q: The log is full on a database; what do i do?
A: The answer depends on the server, the method by which you found out that the log was full, and the job or jobs that filled up the log:
- prod11 has a sp_thresholdaction that saves the file to the SQL Backtrack directory
- peter, new_crimson have sp_thresholdactions that do a truncate_only
- prod's databases are all created w/ data and log on the same segment, thus cannot use sp_thresholdaction
- crimson and crimson_dev are 4.9.2 servers, and don't support sp_thresholdaction

Note also that all our databases have "abort tran on log full" set to true. This is an important option to have; if you don't have it, jobs will go into "log suspend" mode when the log fills and the tendency exists under certain circumstances that the job will get hung, requiring a SQL server reboot.

Log spaces in Sybase serve two purposes; to hold "in progress" working transaction scratchwork when a stored procedure or other complex SQL statement is working, and then to hold the final canonical list of physical changes to the data. Once a stored procedure is completed, all the in progress transactions are cleaned from the log and only the final changes are kept, thus when a complex job is running the log often fills quickly. However once the job has successfully committed itself to disk, the log usually becomes relatively empty again.

Logs typically only fill to 95% full; Sybase leaves them empty enough to allow a dump tran with truncate_only to be able to function. What to do when the log fills up:

If the log fills up on prod11, peter, or new_crimson; check to see if the LCT fired. When sp_thresholdaction runs, it prints a line to the errorlog saying "LCT fired," which in turn is a keyword for the swatch monitors and generates an email message. (if you didn't get an email message, you have a different problem; either sp_thresholdaction isn't working right or your swatchmonitors are messed up). You can check to see if the LCT fired if theres a recently dated transaction log dump for the prod11 database in question in dali:/u/backup/prod11/transaction. Also, check the errorlog (its in $SYBASE/install/[server].errorlog) for a line that says "LCT fired."
Log into the server, do a sp_helpdb [database] and look at the space available in the "log only" segments. If they're relatively empty, then the thresholdaction procedure worked successfully and you have one of two situations:
1. The user was running a job on a database with a halfway full transaction log. The job ran for a while, filled up the log before being able to finish, and aborted. Meanwhile the LCT fired, cleaning up the existing halfway full log, leaving a clean log now. SOLUTION: have the user resubmit the job; it will probably work w/ a totally clean log.
2. If the above doesn't work, then the user-requested job is too big to complete successfully even with a totally clean log. At this point you may extend the log segment by altering the database or have the user recode his/her jobs to do the work in smaller chunks. Often times, the user has coded up a "delete from [table] where date > [somedate]" and the physical changes are massive. Have the user recode to delete in smaller time increments. Having to alter a table is only a last resort (unless of course the log turns out to be sized incorrectly for the database).
If you've done a sp_helpdb [database] and the log segments are still pretty full AND the sp_thresholdaction fired, do a sp_whom to see if any of the jobs are in log suspend. The only way this could have occurred is if the database did NOT have the "abort tran on log full" set. You'll have to try and kill the job.
Sometimes, the LCT job doesn't work right on dali because a non-logged transaction has occurred on the database. (this is only a dali issue because the other servers don't attempt to save their transactions). A non-logged transaction could be a fast-bcp (bcping into a table w/ no indexes) or creating a table via select-into. In this situation, jobs can get hung up. The solution is to perform a dump tran with truncate_only. Since the transaction log chain is fouled up for the rest of the day (until a dump database occurs), no harm will be done that already hasn't been done.
If the log fills up on prod; odds are the log is so completely full you can't even do a dump tran with truncate_only. Log into prod, and do an sp_helpdb on the database; if the data and log segments show 0 free, perform a dump tran [database] with no_log.
If the log fills up on crimson or crimson_dev; sometimes the log can be cleared with a dump tran [db] with truncate_only, but often times the log gets so full that a no_log dump must be done.
Note: DO NOT try to kill a user job that has received a "log full" termination message but is still active; the server is liable to get fouled up trying to reconcile the rolling back of your transaction with the kill request and with the dump tran request issued by the Last Chance threshold.

Various tasks that need maintenance from time to time

Pruning SQL Backtrack control files; they grow each time SQL backtrack runs a job that involves it; thus control files for the trans_dump control directory especially will get huge fast. To clear them you'll need to recreate them, a task described in the FAQ section of the SQL Backtrack page.
Periodically restarting the swatch monitors on the servers w/o automatic nightly restarts (prod, max). This is necessary b/c as you update swatchrc the config file gets distributed but not necessarily restarted.
Update the max:/export/syb_ops/documentation/dump.summary file with results of backups on each server each night (the sybase cron will generate an output file in email to all in the .forward file of the sybase unix account on the machine in question). Assists in tracking time/problems involved w/ dumps.
Cleaning up $SYBASE environments; whenever you make a configuration change or an interfaces file change, a backup copy of $SYBASE/interfaces and/or $SYBASE/[server].cfg gets made. We move these files to the $SYBASE/config_backup directory for storage/archival. Make sure to leave the highest numbered version in the $SYBASE directory so the system doesn't reset your archival numbers.
Update files in max:/export/syb_ops/documentation when you make any types of system changes. If you make any configuration changes to the servers, such as adding a device or creating a database, make sure to edit the appropriate server's ddl file.
Deleting old transaction log files; a script runs each night (see the nightly page for more details) and cleans up transaction files based on the success of the backup...but if the database dump fails we keep the transaction log files in case of a database failure that next day. The downside is, those transaction log files must be manually deleted. So, once you get a successful dump you'll have to delete the previous day's transaction log files. This is currently only an issue on prod11 and crimson (though neither server has had a database dump failure in weeks).