ftp.pasteur.org/FAQ/

home *** CD-ROM | disk | FTP | other *** search

/ ftp.pasteur.org/FAQ/ / ftp-pasteur-org-FAQ.zip / FAQ / databases / sybase-faq / part11 < prev next >

Wrap

Internet Message Format | 2004-04-21 | 60.6 KB

Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail Message-ID: <databases/sybase-faq/part11_1082468590@rtfm.mit.edu> Supersedes: <databases/sybase-faq/part11_1074677126@rtfm.mit.edu> Expires: 2 Aug 2004 13:43:10 GMT References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu> X-Last-Updated: 2003/03/02 From: dowen@midsomer.org (David Owen) Newsgroups: comp.databases.sybase,comp.answers,news.answers Subject: Sybase FAQ: 11/19 - Issues, dbccs, isql, bcp Reply-To: dowen@midsomer.org (David Owen) Followup-To: comp.databases.sybase Distribution: world Organization: Midsomer Consultants Inc. Approved: news-answers-request@MIT.EDU Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP Originator: faqserv@penguin-lust.MIT.EDU Date: 20 Apr 2004 13:45:10 GMT Lines: 1204 NNTP-Posting-Host: penguin-lust.mit.edu X-Trace: 1082468710 senator-bedfellow.mit.edu 569 18.181.0.29 Xref: senator-bedfellow.mit.edu comp.databases.sybase:106209 comp.answers:56955 news.answers:270295 Archive-name: databases/sybase-faq/part11 URL: http://www.isug.com/Sybase_FAQ Version: 1.7 Maintainer: David Owen Last-modified: 2003/03/02 Posting-Frequency: posted every 3rd month A how-to-find-the-FAQ article is posted on the intervening months. Platform Specific Issues - Solaris 2.1.1 Should I run 32 or 64 bit ASE with Solaris? 2.1.2 What is Intimate Shared Memory or ISM? Platform Specific Issues - NT Performance and Tuning ASE FAQ ------------------------------------------------------------------------------- 2.1.1: Should I run 32 or 64 bit ASE with Solaris? ------------------------------------------------------------------------------- Sybase' first forray into 64-bit was with release 11.9.3. I do not know much about that release, but I seem to remember that it was always lagging behind its sister release of 11.9.2. With ASE 12, Sybase have both 32-bit and 64-bit versions at the same release level. This is a big improvement, since it cuts out some concern that was prevelant with 11.9.3 as to why they were on different numbers. The releases are supposed to be identical in terms of functionality, save the fact that the 64-bit version can address more memory. So, why not just be done with it and have just the one version? Firstly, I suppose that not everyone who can run Solaris has the capability to run the 64-bit version. There are still a lot of 32-bit Sparc chips around and a lot of people use them. It is also possible to run 32-bit Solaris on a 64-bit machine. In order to be able to run 64-bit Sybase you will have to be running 64-bit Solaris. If you have a 64-bit environment, you still need to choose between which Sybase version to run. If you have more than 4G bytes of memory on your machine and you would like Sybase to take advantage of it, then the 64-bit version is for you. If not, then the word on the street, and from Sybase themselves, is that in identical environments, the 32-bit version runs slightly faster. I have heard a couple of explanations as to why this is so, but nothing that I find 100% convincing. Back to top ------------------------------------------------------------------------------- 2.1.2: What is Intimate Shared Memory or ISM? ------------------------------------------------------------------------------- Intimate Shared Memory or ISM is a specific feature of Sun Solaris. The feature was developed so that when multiple processes (at OS level) try to access a shared memory region, they do not use multiple TLBs (Transalation Lookaside Buffers) at OS kernel level. This saves lot of kernel memory space. I don't think that does a whole lot for Sybase, more for Oracle I suppose. However, there is a side effect that is useful. If there is engough memory available on the machine, typically Solaris will not swap out process memory marked as ISM if it can possibly help it. Swapping in Solaris is done in three phases, reserved, allocated and used. Locking the shared memory has the advantage of increasing performance. Of course, if there are lot's of processes on the machine and if new processes starve for memory, there is a potential that ISM will get swapped. For performance reasons, it is worth ensuring that Sybase can allocated its shared memory segment using ISM. ASE tries by default to use ISM and will display an error message during start up if this is not possible. It is probably worth starting Sybase soon after a machine is rebooted to give it the best possible chance of using ISM. More details can be found on the Sunsolve web site. I don't have a URL, sorry. I am not even sure if this is a public site or not. Back to top ------------------------------------------------------------------------------- Platform Specific Issues - NT Performance and Tuning ASE FAQ Platform Specific Issues - NT/2000 2.2.1 How to Start ASE on Remote NT Servers 2.2.2 How to Configure More than 2G bytes of Memory for ASE on NT 2.2.3 Installation Issues Platform Specific Issues - Linux Platform Specific Issues - Solaris ASE FAQ ------------------------------------------------------------------------------- 2.2.1: How to Start ASE on Remote NT Servers ------------------------------------------------------------------------------- Currently, there is no method of starting ASE on a remote NT server using Sybase Central. So how do you get ASE running on an NT server located in one city when you are currently located in another. OK, OK, so flying there is an option, but let's try to stay within the realms of practicality <g>. One option is to buy a good telnet server and telnet onto the box and then start it using the "RUN_<server>.BAT" file. This works, but depending on the telnet server can be a little troublesome. NT does not have such a nice set of commands as Unix, so there is no "startserver" to run the server in the background. This means that the telnet window that you use to start the server may have to stay open for the lifetime of the server. This means that the health of ASE is now dependent upon two machines not crashing. As I say, your mileage may vary, but I have certainly found this to be the case with at least one telnet server. Another option is to use SRVMGR.EXE from the Windows NT resource kit. Roughly you issue srvmgr \\SERVER-TO-BE-MANAGED (obviously replacing SERVER-TO-BE-MANAGED with the name of the server you wish to start ASE on!) Select the "Services" option, and start ASE as if you were in the "Services" applet on a local NT server. Yet another option is to install PC Anywhere or VNC on both machines and use one of these tools to remotely control the system. (VNC is a very good version of PC Anywhere, except that the clients and servers run on NT, Unix, Linux; the source code is available and it is free (in both senses of the word)!) If anyone knows of any better methods, please let me know and I will add them to this section. Thanks. Back to top ------------------------------------------------------------------------------- 2.2.2: How to Configure More than 2G bytes of Memory for ASE on NT. ------------------------------------------------------------------------------- The following was posted on news://forums.sybase.com/sybase.public.ase.nt , taken directly from a Sybase SPS case notes. (I read recently that this is not needed, that Sybase does all of this for you before it leaves the factory. If anyone knows the real answer, I would be grateful for an update.) If you are using NT server enterprise, or Windows 2000 Advanced Server, you may be able to get up to 3gig: Here is what you need to do in order to configure greater than 2GB memory for ASE on NT: Step 1: Make a backup copy of sqlsrvr.exe in the sybase bin directory Step 2: Verify the current settings of sqlsrvr.exe using imagecfg.exe: imagecfg sqlsrvr.exe sqlsrvr.exe contains the following configuration information: Subsystem Version of 4.0 Stack Reserve Size: 0x20000 Stack Commit Size: 0x4 Step 3: Use imagecfg to switch on large addressing using the -l (lowercase L) switch: imagecfg -l sqlsrvr.exe sqlsrvr.exe contains the following configuration information: Subsystem Version of 4.0 Stack Reserve Size: 0x20000 Stack Commit Size: 0x4 sqlsrvr.exe updated with the following configuration information: Subsystem Version of 4.0 Image can handle large (>2GB) addresses Stack Reserve Size: 0x20000 Stack Commit Size: 0x4 Step 4: verify ASE is able to start Step 5: The NT machine must be booted with the /3GB flag and must have sufficient paging file space (e.g., if you want ASE to access 3G of memory then the paging file must be at least that size) Step 6: increase total memory to say 2.2 gb (anything > 2gb) Step 7: increase starting virtual memory address to 23662592 decimal (which is 1691000 hex) as shown: sp_configure 'shared memory starting address', 23662592 Step 8: restart server Step 9: test to connect a lot of users (more than 240) Back to top ------------------------------------------------------------------------------- 2.2.3: Installation issues. ------------------------------------------------------------------------------- This is a list of items to be aware of when installing ASE onto NT/2000. * Make sure that you install onto a local drive. This might not affect all versions of ASE on NT/2000, but I could not get the software to install and run from a network drive with the 12.5 developer edition. Try as I might, it kept failing without really telling me why. I aborted the installation, installed onto one of the local drives, and it worked a charm. My only NT/ 2000 machine is my laptop with only one drive, so I do not know if this is any drive other than "C" or whether it is just network mounted drives. Will be happy to take advice and corrections from Sybase or anyone that can tell me what I was doing wrong. Back to top ------------------------------------------------------------------------------- Platform Specific Issues - Linux Platform Specific Issues - Solaris ASE FAQ Platform Specific Issues - Linux 2.3.1 ASE on Linux FAQ DBCCs Platform Specific Issues - NT ASE FAQ ------------------------------------------------------------------------------- 2.3.1: ASE on Linux FAQ ------------------------------------------------------------------------------- There is an FAQ covering ASE on Linux at Michael Peppler's site. http://www.mbay.net/~mpeppler/Linux-ASE-FAQ.html It contains a fair bit of information about running Sybase ASE on Linux and if you are interested in doing just that, then go read it. It certainly will answer your question about why, after a new install, you can connect from the server that ASE is installed on but no other client. (I am not going to tell you here, you will have to go and read it :-) Back to top ------------------------------------------------------------------------------- DBCCs Platform Specific Issues - NT ASE FAQ DBCC's 3.1 How do I set TS Role in order to run certain DBCCs...? 3.2 What are some of the hidden/trick DBCC commands? 3.3 Other sites with DBCC information. 3.4 Fixing a Munged Log Performing any of the above may corrupt your ASE installation. Please do not call Sybase Technical Support after screwing up ASE. Remember, always take a dump of the master database and any other databases that are to be affected. isql Platform Specific Issues - Linux ASE FAQ Index ------------------------------------------------------------------------------- 3.1: How to set TS Role ------------------------------------------------------------------------------- Some DBCC commands require that you set TS Role in order to run them. Here's how to set it: Login to Server as sa and perform the following: sp_role "grant", sybase_ts_role, sa go set role "sybase_ts_role" on go Back to top ------------------------------------------------------------------------------- 3.2: DBCC Command Reference ------------------------------------------------------------------------------- Here is the list of DBCC commands that have been sent into the FAQ. If you know of any more or have more information, then please send it in to dowen@midsomer.org, this is, after all, a resource for us all. As ASE develops, so some of the dbcc's change. I have pointed out major changes from one release to another that I know about. However, a couple of changes are so common that it will save a lot of space if I say it once. Where there is an option to specify dbid or dbname, in previous releases only dbid would be accepted. +--------------------------------------------------------------------------------------------------------------+ | | | |Risk Level| | DBCC Name | Argument List | Comments | / | | | | |Supported?| |------------------+-----------------------------------------------------+--------------------------+----------| |allocdump |( dbid | dbname, page ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | |( { print_bufs | no_print }, bucket_limit ) |Format prior to ASE 11. | | | |-----------------------------------------------------+--------------------------+----------| |bhash | |Format prior to ASE 12. | | | |-----------------------------------------------------+--------------------------+----------| | |( cname [, clet_id [, { print_bufs | no_print |Format ASE 12 and later. | | | |},bucket_limit]] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | |( [ dbid ][, objid ][, nbufs ], printopt = {0 | 1 | |Format prior to ASE 11. | | | |2},buftype) | | | | |-----------------------------------------------------+--------------------------+----------| | |[ (dbid | dbname [, objid | objname [, nbufs [, | | | | |printopt = { 0 | 1 | 2 } |Format prior to ASE 12. | | | |[, buftype = { kept | hashed | nothashed | ioerr} [, | | | |buffer |cachename ] ] ] ] ] ) ] | | | | |-----------------------------------------------------+--------------------------+----------| | |[ (dbid | dbname [, objid | objname [, nbufs [, | | | | |printopt = { 0 | 1 | 2 } | | | | |[, buftype = { kept | hashed | nothashed | ioerr} [, |Format ASE 12 and later. | | | |cachename [, cachelet_id ] | | | | |] ] ] ] ] ) ] | | | |------------------+-----------------------------------------------------+--------------------------+----------| | |( startaddress, length ) |Format prior to ASE 12. | | |bytes |-----------------------------------------------------+--------------------------+----------| | |(startaddress, length [, showlist | STRUCT_NAME]) |Format ASE 12 and later. | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |Uninstall and Uncache | | |cacheremove |(dbid|dbname, objid|objname) |descriptor for an object | | | | |from cache | | |------------------+-----------------------------------------------------+--------------------------+----------| |checkalloc |[( dbname [, fix | nofix ] ) ] | | | |------------------+-----------------------------------------------------+--------------------------+----------| |checkcatalog |[( dbname )] | | | |------------------+-----------------------------------------------------+--------------------------+----------| |checkdb |[( dbname [, skip_ncindex ] ) ] | | | |------------------+-----------------------------------------------------+--------------------------+----------| |checktable |( tablename | tabid [, skip_ncindex ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |Error can take one of the | | | | |following values: | | | | | | | | | | * 1133 error | | | | | demonstrates that a | | | | | page we think is an | | | | | oam is not | | | | | * 2502 error shows | | | | | multiple references to| | | | | the same page | | | | | * 2503 error shows a | | | | | breakage in the page | | | | | linkage | | | | | * 2521 error shows that | | | | | the page is referenced| | | | | but is not allocated | | | | | on the extent page | | | | | * 2523 error shows that | | | | | the page number in the| | | | | page or catalog | | | | | entries are | | | | | out-of-range for the | | | | | database | | | | | * 2525 error shows that | | | | | an extent objid/indid | | | | | do not match what is | | | | | on the page | | | | | * 2529 error shows a | | |corrupt |( tablename, indid, error ) | page number | | | | | out-of-range for the | | | | | database or a 605 | | | | | style scenario | | | | | * 2540 error occurs when| | | | | a page is allocated on| | | | | an extent but the page| | | | | is not referenced in | | | | | the page chain | | | | | * 2546 error occurs when| | | | | an extent is found for| | | | | an object without an | | | | | of its pages being | | | | | referenced (a stranded| | | | | extent) | | | | | * 7939 error occurs when| | | | | an allocation page | | | | | which has extents for | | | | | an object are not | | | | | reflected on the OAM | | | | | page | | | | | * 7940 error occurs when| | | | | the total counts in | | | | | the OAM page differ | | | | | from the actual count | | | | | of pages in the chain | | | | | * 7949 error is similar | | | | | to a 7940 except that | | | | | the counts are on an | | | | | allocation page basis | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |cursor_level - level of | | |cursorinfo |(cursor_level, cursor_name) |nesting. -1 is all nesting| | | | |levels | | |------------------+-----------------------------------------------------+--------------------------+----------| |dbinfo |( [ dbname ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |dbrepair |( dbid, option = { dropdb | fixindex | fixsysindex },| | | | |table, indexid ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |dbrepair |( dbid, ltmignore) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |dbtable |( dbid ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |delete_row |( dbid, pageid, delete_by_row = { 1 | 0 }, rownum ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |des |( [ dbid ][, objid ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |eng func may be: | | | | | | | |engine |(eng_func) | * "online" | | | | | * "offline", ["<engine | | | | | number>"] | | |------------------+-----------------------------------------------------+--------------------------+----------| |extentcheck |( dbid, objid, indexid, sort = {1|0} ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |extentdump |( dbid, page ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |extentzap |( dbid, objid, indexid, sort ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |findnotfullextents|( dbid, objid, indexid, sort = { 1 | 0 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |fix_al |( [ dbname ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |help |( dbcc_command ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |ind |( dbid, objid, printopt = { 0 | 1 | 2 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |indexalloc |(tablename|tabid, indid, [full | optimized | fast], | | | | |[fix | nofix]) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |listoam |(dbid | dbname, tabid | tablename, indid) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |locateindexpgs |( dbid, objid, page, indexid, level ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |lock | |print out lock chains | | |------------------+-----------------------------------------------------+--------------------------+----------| |log |( [dbid][,objid][,page][,row][,nrecords][,type= | | | | |{-1..36}],printopt={0|1} ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |memusage | | | | |------------------+-----------------------------------------------------+--------------------------+----------| |netmemshow |( option = {1 | 2 | 3} ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |netmemusage | | | | |------------------+-----------------------------------------------------+--------------------------+----------| |newalloc |( dbname, option = { 1 | 2 | 3 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |page |( dbid, pagenum [, printopt={0|1|2} ][, cache={0|1} ]| | | | |[, logical={1|0} ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |pglinkage |( dbid, start, number, printopt={0|1|2}, target, | | | | |order={1|0} ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |pktmemshow |( option = {spid} ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |procbuf |( dbid, objid, nbufs, printopt = { 0 | 1 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |prtipage |( dbid, objid, indexid, indexpage ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |pss |( suid, spid, printopt = { 1 | 0 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |rebuildextents |( dbid, objid, indexid ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |careful as this will cause| | |rebuild_log |( dbid, 1, 1) |large jumps in your | | | | |timestamp values used by | | | | |log recovery. | | |------------------+-----------------------------------------------------+--------------------------+----------| |remap | |Only available prior to | | | | |12. | | |------------------+-----------------------------------------------------+--------------------------+----------| |resource | | | | |------------------+-----------------------------------------------------+--------------------------+----------| |setkeepalive |(# minutes) |for use on Novell with TCP| | | | |/IP. | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |Not needed with more | | | | |recent versions of ASE, | | | | |use the supplied stored | | | | |procs. On older versions | | |settrunc |('ltm','ignore') |of ASE (pre-11?) this | | | | |command may be useful for | | | | |a dba who is dumping and | | | | |loading a database that | | | | |has replication set on for| | | | |the original db. | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |Shows the sql that the | | |sqltext |(spid) |spid is currently | | | | |running. Blank if idle. | | |------------------+-----------------------------------------------------+--------------------------+----------| |stacktrace |(spid) |Not Linux, yet :-) | | |------------------+-----------------------------------------------------+--------------------------+----------| |show_bucket |( dbid, pageid, lookup_type ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |tab |( dbid, objid, printopt = { 0 | 1 | 2 } ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |tablealloc |(tablename|tabid, [full | optimized | fast],[fix | | | | | |nofix]) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |traceoff |( tracenum [, tracenum ... ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| |traceon |( tracenum [, tracenum ... ] ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | | |Used to switch on/off | | | | |certain options. Some are| | | | |supported and listed in | | | | |the docs, others | | | | |correspond to the | | | | |buildmaster -yall name | | | | |minus the c prefix. | | | | | | | | | |Supported: | | | | | | | | | | * ascinserts ('value' is| | | | | again two values, 1|0 | | | | | for on or off and the | | | | | table name). | | | | | * cpuaffinity | | | | | ('value' in this case | | |tune |( option, value ) | is two values, the | | | | | starting cpu number | | | | | and "on" or "off".) | | | | | * maxwritedes | | | | | | | | | |Unsupported: | | | | | | | | | | * indextrips | | | | | * oamtrips | | | | | * datatrips | | | | | * schedspins | | | | | * bufwashsize | | | | | * sortbufsize | | | | | * sortpgcount | | | | | * maxscheds | | | | | * max_retries | | | | | | | | | | | | |------------------+-----------------------------------------------------+--------------------------+----------| |undo |( dbid, pageno, rowno ) | | | |------------------+-----------------------------------------------------+--------------------------+----------| | |( dbid|dbname, type = {0|1}, display_opts = {0|1} [, |If sp_helpdb is returning | | |usedextents |bypiece = {0|1}]) |negative free space, try: | | | | |usedextents(dbid, 0, 1, 1)| | +--------------------------------------------------------------------------------------------------------------+ Back to top ------------------------------------------------------------------------------- 3.3: Other Sites with DBCC information ------------------------------------------------------------------------------- * http://user.icx.net/~huntley/dbccinfo.htm, Al Huntley's site contains a comprehensive including discussion on some and example output. * http://www.kaleidatech.com/dbcc1.htm, From KaleidaTech Associates, Inc. has another fairly complete list. * http://www.sypron.nl, as you would expect, Rob Verschoor has a list of DBCC's in his ASE Quick Reference Supplement. Back to top ------------------------------------------------------------------------------- 3.4: Fixing a Munged Log ------------------------------------------------------------------------------- Sybase Technical Support states that this is extremely dangerous as it "jacks up the value of the timestamp" which is used for recovery purposes. This may cause potential database corruption if the system fails while the timestamp rolls over. In 4.9.2, you could only run the dbcc rebuild_log command once and after that you would have to use bcp to rebuild the database In System 10, you can run this command about 10 times. In System 11 I (Pablo, previous editor) tried it about 20 times and no problem. 1> use master 2> go 1> select count(*) from your_database..syslogs 2> go ----------- some number 1> sp_configure "allow updates",1 2> go 1> reconfigure with override /* for system 10 and below only*/ 2> go 1> begin tran 2> go /* Save the following status to be used later... */ 1> select saved_status=status from sysdatabases where name = "your_database" 2> go 1> update sysdatabases set status = -32768 where name = "your_database" 2> go 1> commit tran 2> go 1> shutdown 2> go 1> dbcc rebuild_log (your_database, 0, 0) 2> go DB-LIBRARY error (severity 9): Unexpected EOF from SQL Server. 1> dbcc rebuild_log (your_database, 1, 1) 2> go DBCC execution completed. If DBCC printed error messages, see your System Administrator. 1> use your_database 2> go 1> select count(*) from syslogs 2> go ----------- 1 1> begin tran 2> go 1> update sysdatabases set status = saved_status where name = "your_database" 2> go (1 row affected) 1> commit tran 2> go 1> shutdown 2> go Back to top ------------------------------------------------------------------------------- isql Platform Specific Issues - Linux ASE FAQ Index isql 4.1 How do I hide my password using isql? 4.2 How do I remove row affected and/or dashes when using isql? 4.3 How do I pipe the output of one isql to another? 4.4 What alternatives to isql exist? 4.5 How can I make isql secure? bcp DBCCs ASE FAQ ------------------------------------------------------------------------------- 4.1: Hiding your password to isql ------------------------------------------------------------------------------- Here are a menagerie (I've always wanted to use that word) of different methods to hide your password. Pick and choose whichever fits your environment best: Single ASE on host Script #1 Assuming that you are using bourne shell sh(1) as your scripting language you can put the password in a file and substitute the file where the password is needed. #!/bin/sh # invoke say ISQL or something... (cat $HOME/dba/password_file cat << EOD dbcc ... go EOD ) | $SYBASE/bin/isql -Usa -w1000 Script #2 #!/bin/sh umask 077 cat <<-endOfCat | isql -Umyuserid -Smyserver mypassword use mydb go sp_who go endOfCat Script #3 #!/bin/sh umask 077 cat <<-endOfCat | isql -Umyuserid -Smyserver `myScriptForGeneratingPasswords myServer` use mydb go sp_who go endOfCat Script #3 #!/bin/sh umask 077 isql -Umyuserid -Smyserver <<-endOfIsql mypassword use mydb go sp_who go endOfIsql Script #4 #!/bin/sh umask 077 isql -Umyuserid -Smyserver <<-endOfIsql `myScriptForGeneratingPasswords myServer` use mydb go sp_who go endOfIsql Script #5 #!/bin/sh echo 'mypassword use mydb go sp_who go' | isql -Umyuserid -Smyserver Script #6 #!/bin/sh echo "`myScriptForGeneratingPasswords myServer` use mydb go sp_who go" | isql -Umyuserid -Smyserver Script #7 #!/bin/sh echo "Password :\c " stty -echo read PASSWD stty echo echo "$PASSWD waitfor delay '0:1:00' go " | $SYBASE/bin/isql -Usa -S${DSQUERY} Multiple ASEs on host Again, assuming that you are using bourne shell as your scripting language, you can do the following: 1. Create a global file. This file will contain passwords, generic functions, master device for the respective DSQUERY. 2. In the actual scripts, source in the global file. Global File SYBASE=/usr/sybase my_password() { case $1 in SERVER_1) PASSWD="this";; SERVER_2) PASSWD="is";; SERVER_3) PASSWD="bogus;; *) return 1;; esac return 0 } Generic Script #!/bin/sh -a # # Use "-a" for auto-export of variables # # "dot" the file - equivalent to csh() "source" command . $HOME/dba/global_file DSQUERY=$1 # Determine the password: sets PASSWD my_password $DSQUERY if [ $? -ne 0 ] ; then # error! echo "<do some error catching>" exit 1 fi # invoke say ISQL or something... echo "$PASSWD dbcc ... go" | $SYBASE/bin/isql -U sa -S $DSQUERY -w1000 Back to top ------------------------------------------------------------------------------- 4.2: How to remove row affected and dashes ------------------------------------------------------------------------------- If you pipe the output of isql then you can use sed(1) to remove this extraneous output: echo "$PASSWD sp_who go" | isql -U sa -S MY_SERVER | sed -e '/affected/d' -e '/---/d' If you simply wish to eliminate the row affected line use the set nocount on switch. Back to top ------------------------------------------------------------------------------- 4.3: How do I pipe the output of one isql to another? ------------------------------------------------------------------------------- The following example queries sysdatabases and takes each database name and creates a string of the sort sp_helpdb dbname and sends the results to another isql. This is accomplished using bourne shell sh(1) and sed(1) to strip unwanted output (see Q4.2): #!/bin/sh PASSWD=yuk DSQUERY=GNARLY_HAIRBALL echo "$PASSWD print \"$PASSWD\" go select 'sp_helpdb ' + name + char(10) + 'go' from sysdatabases go" | isql -U sa -S $DSQUERY -w 1000 | \ sed -e '/affected/d' -e '/---/d' -e '/Password:/d' | \ isql -U sa -S $DSQUERY -w 1000 To help you understand this you may wish to comment out any series of pipes and see what output is being generated. Back to top ------------------------------------------------------------------------------- 4.4: Are there any alternatives to isql? ------------------------------------------------------------------------------- sqsh In my opinion, and that of quite a lot of others, this is the most useful (direct) replacement for isql that exists. It combines the usefulness of a good shell with database interaction. Looking for the ability to page the output of a long command? Look no further. Need to search a result set using a regular expression? This is the tool for you. Like isql, sqsh is a command line tool. It supports all of the features and switches of isql with myriad of its own. There is one feature that isql has the sqsh does not, and that is the ability to read the password as the first line of an input file. If you look at a lot of the examples above, the password is piped in, sqsh does not support this with the latest release. I am not sure if this is a deliberate feature or not. A quick summary of its features: 1. command line editing; 2. command history; 3. ability to pipe to standard filters; 4. ability to redirect output to X window; 5. shell variables 6. background execution; Like all good modern shells, sqsh supports command line editing. You need to have the GNU readline library available on your machine, but that is now becoming common. If you have the bash shell, you have it by default I believe. Sqsh behaves very well if run in an X Windows environment. There is the direct support by way of an output switch to go that sends the results to an X Window, but it is much better than that. If you resize the screen sqsh also resizes its internal width to take advantage of the new size, just like any well behave X application. Doesn't sound like a lot, but when you want to see the results from a query and understand the output easily, much better if the columns all line up and don't wrap. With isql you would have to exit the program, run it again with an adjust '-w' flag and rerun the query. Enough said. You need to try it! You can grab it from the official SQSH website http://www.sqsh.org. There are a host of others that I have heard about, but can no longer get to. Some are mentioned in various sites, mainly the sqsh site. If any of them are important, still being maintained, are actively supported, and are available somewhere, then let me know and I will update this list. * dsql * asql * ctsql * qisql However, I suspect that provided we have sqsh, no other command line version is needed!! SQL Advantage This was Sybase's second attempt at a true GUI based SQL editor. It was only available for W86 platforms. Quite a lot of people liked it, it came free with Sybase and did just about the minimum necessary for an SQL Editor. Sadly, I cannot find my copy any more, since 12.5 for NT no longer has it. I have heard several unofficial channels say that Sybase will let you have a copy if you ask. I do not know since I have not asked. Not having a copy, and having a bad memory, I cannot tell you all of its features. I cannot remember syntax highlighting or anything fancy like that, but that does not mean that it was not there. I know that there are some true devotees and if one of you cares to send me some words, I will slap them in here. There was a GUI before SQL Advantage, but it is/was too dire to mention. jisql This is the latest release from Sybase for the desktop interactive shell. It uses Java, but you probably guessed that from the name. It works fine and is a little like SQL Advantage (which was a little like Data Work Bench, which was a ...), from what I remember of that tool. Correct me if I am wrong Anthony!! The best thing about it is that it is available for all platforms that support Java. The worst thing about it, and this is not so much a fault of jisql as a fault of Java in general, is that it is unable to use the interfaces file. I know that Java is intended to be truly multi-platform and that your average photocopier does not have access to environment variables, but how many photocopiers run Sybase? In most installations I can find my way totally painlessly from ASE server to ASE server, not worrying about ports etc. If you start using jisql regularly you will soon know the port numbers, since it is the only way that you can connect. Personally, until this is solved, I will not use the bloody tool. tsql This is the command line client that comes with FreeTDS. It comes with the FreeTDS client (http://www.freetds.org). It is a very simple client, but it works. ASSE Developed by Manish I Shah to be a direct replacement for Data Workbench, but in Java. It is still in alpha, I believe, at Sourceforge. Suffers the same pros and cons as jisql simply because of its Java heritage. wisqlite This is similar to jisql in its functionality, but is written in Tcl/Tk. I am not 100% sure of the status, but will update this paragraph when I am. Try Tom Poindexter's site for a starting point. ntquery This is a very lightweight SQL Editor that is someway between Sybase's original offering (whose name I have had cleaned from my brain using hypnosis) and SQL Advantage. I am not sure who wrote it but it is free, runs on W86 platforms only and is available from ftp://ftp.midsomer.org/pub/ntquery.zip DWB The father of them all. I am not sure if this is officially allowed to circulate, but I know some people that still use it and like it. I am petitioning Sybase to allow me to make it available. It is only available for Sun, or at least the version that I have is Sun only, but it is quite a nice tool all the same. Back to top ------------------------------------------------------------------------------- 4.4: How do I make isql secure? ------------------------------------------------------------------------------- Isql uses the open/client libraries, which have no built in means of securing the packets that I know of. However, it is possible to use ssh to do all of the work for you. It is really quite straightforward. I saw this first published on the Sybase-L list by Tim Ellis, so all of the credit gos to him. 1. You will need a server running sshd that you have access to, which also has access to the ASE server. 2. Choose a port that you are going to make your secure connection from. Just like all ASE port selections it is totally arbitrary, but you if you were setting up a number of these, then you might want to think about a strategy. Regular server + 100 or something. Just make sure that it does not, and will not, clash with any of your regular servers. 3. Edit the interfaces file on the client side and set up a new server with an IP address of localhost and the port number you chose in the previous point. You might want to call it SERVER_SSH just to make sure that you know that it is the secure one. 4. Run the following ssh command: ssh -2 -N -f -L port_chosen_above:remote_server:remote_port user@ssh.server.com 5. Connect to the server using isql -Uuser -SSERVER_SSH In the ssh line, the -2 means use that version of the protocol (obviously it must be supported by your client and server). -f forces the ssh into the background. Not supported by version 1 only clients. -N means do not prompt for input. Again, this is not supported by version 1 clients. The user@ssh.server.com refers to the sshd server that you have access to. Let us look at an example. You have a server running ASE on port 4100. (Make sure that this port is *not* visible from the outside world, otherwise it is wide open to people attacking it directly.) I have not tried all of the ins and outs of this, I am happy to take advice, but on this same machine you have a copy of sshd running that you can see from the outside world. Choose another port that you are going to have as your secure port. Let's call it 5100 for the sake of argument. Edit the interfaces file on the client machine (which is presumably somewhere in untrusted land, say a client site) and add a new server, lets call it MYSERVER_SSH and have it listen on localhost,5100. Now execute the ssh magic, again from the client machine: ssh -2 -N -f -L 5100:myserver.com:4100 sybase@myserver.com Now connect to it using isql -Usa -SMYSERVER_SSH and you should get the familiar 1> prompt. All traffic to and from the server is going via an SSH tunnel, and so can be considered relatively secure. Back to top ------------------------------------------------------------------------------- bcp DBCCs ASE FAQ bcp 5.1 How do I bcp null dates? 5.2 Can I use a named pipe to bcp/dump data out or in? 5.3 How do I exclude a column? next prev ASE FAQ ------------------------------------------------------------------------------- 5.1: How do I bcp null dates? ------------------------------------------------------------------------------- As long as there is nothing between the field delimiters in your data, a null will be entered. If there's a space, the value will be Jan 1, 1900. You can use sed(1) to squeeze blanks out of fields: sed -e 's/|[ ]*|/||/g' old_file > new_file Back to top ------------------------------------------------------------------------------- 5.2: Can I use a named pipe to bcp/dump data out or in? ------------------------------------------------------------------------------- System 10 and above. If you would like to bcp copy from one table to a named pipe and compress: 1. %mknod bcp.pipe p 2. %compress sysobjects.Z & 3. %bcp master..sysobjects out bcp.pipe -c -U .. > bcp.pipe 4. Use ps(1) to determine when the compress finishes. To bcp from my1db..dummy_table_1 to my2db..dummy_table_2: 1. %mknod bcp.pipe p 2. %bcp my2db..dummy_table_2 in bcp.pipe -c -U .. & To avoid confusion between the above bcp and the next, you may choose to either use a separate window or redirect the output to a file. 3. %bcp my1db..dummy_table_1 out bcp.pipe -c -U .. Back to top ------------------------------------------------------------------------------- 5.3: How do I exclude a column? ------------------------------------------------------------------------------- Open/Client 11.1.1 Create a view based on the table that you want to exclude a column from and then bcp out from the view. Open/Client Versions Older Than 11.1.1 The documentation Utility programs for Unix describes the use of format files, including the field Server Column Order. Server Column Order must equal the colid of the column, or 0 if the host file field will not be loaded into any table column. I don't know if anyone has got this feature to work. So, here is another way of removing the column. In your example, you want to remove the last column. I am going to include another example to remove the second column and include a fourth column. Why? Because it is harder. First example will deal with removing the last column. Removing the Last Column Edit your bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data: --- bcpout.fmt 10.0 2 <------------------ Changed number of columns to BCP to two 1 SYBINT4 0 4 "<**>" 1 counter 2 SYBCHAR 1 512 "\n" 2 text1 <--- Replaced <**> with \n 3 SYBCHAR 1 512 "\n" 3 text2 <--- DELETE THIS LINE Now recreate the table with the last column removed and use the same bcpout.fmt file to BCP back in the data. Now let's try removing the second column out four columns on a table. Removing the Second out of Four Columns Edit the bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data: --- bcpout.fmt 10.0 3 <------------------ Changed number of columns to BCP to three 1 SYBINT4 0 4 "<**>" 1 counter 2 SYBCHAR 1 512 "<**>" 2 text1 <--- DELETE THIS LINE 2 SYBCHAR 1 512 "<**>" 3 text2 <--- Changed number items to 2 3 SYBCHAR 1 512 "\n" 4 text3 <--- Changed number items to 3 Including the Fourth Column Now copy the bcpout.fmt to bcpin.fmt, recreate table with col 2 removed, and edit bcpin.fmt file: --- bcpin.fmt 10.0 3 1 SYBINT4 0 4 "<**>" 1 counter 2 SYBCHAR 1 512 "<**>" 2 text2 <-- Changed column id to 2 3 SYBCHAR 1 512 "\n" 3 text3 <-- Changed column id to 3 ------------------------------------------------------------------------------- Back to top next prev ASE FAQ