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?

  1. 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.
  2. If you're stopping prod11, see the procedures below for stopping the Integration Channel jobs BEFORE you shutdown prod11.
  3. 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.
  4. Log into the SQL server as user sa and execute:
    1> sp_who

    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.
  5. If you're satisfied the server is ready to shutdown, execute:
    1> shutdown

    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.

  6. 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

    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.

  7. 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.

  8. 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.

Back to top

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
---------- ----- ----------------- ----------------- ------ ------- ------ ----
           95084 1997:8:14:16:57:2 1997:8:19:10:25:4   1993       0      6 NaNQ
           24018 1997:8:14:17:1:31 1997:8:19:10:24:3   1997       6      6 NaNQ
           31456 1997:8:14:17:1:41 1997:8:19:10:25:1   1993       5      5 NaNQ
           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.

Back to top

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

Back to top

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.

Back to top

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:

Back to top

Various tasks that need maintenance from time to time Back to top