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:
Q: How do i stop the Sybase SQL server?
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.
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...):
% 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 9751then 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 9751kill -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.
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.
% serversand 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 NaNQThe two Server names we're interested in are "TO_ADT_DB" and "ToRepository."
% 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.
Q: Now, how do i restart my Sybase SQL server?
A: As Unix user sybase,
% cd $SYBASE % cd install % lsIn 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]
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]
1> sp_adduser [loginname]
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]
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> goIf you want to change sa's password, you can just leave off the third field of this statement.
1> sp_password "oldpassword", "newpassword" 2> goNote 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.
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: