Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes I have gone through documentation and previous stackoverflow questions but not findings those helpful.
Postgres version 10.6
Even for index only scans values of idx_tup_fetch is increasing. So I am not able to understand difference between idx_tup_read and idx_tup_fetch.
Initially understanding was:
idx_tup_read: matching records read from index.
idx_tup_fetch: live rows fetched from table after records are matched on index. So my understanding is that for index only scan since no data is fetched from table so this should not increment.
But when I ran the tests then this understanding is not true.
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        4 |            2 |             2
(1 row)

postgres=> select * from test_monika where rank=100;
 id | name | rank
----+------+------
 95 |      |  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        5 |            3 |             3
(1 row)

postgres=> select rank from test_monika where rank=100;
 rank
------
  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        6 |            4 |             4
(1 row)

postgres=> select id from test_monika where rank=100;
 id
----
 95
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        7 |            5 |             5
(1 row)

postgres=> select 1 from test_monika where rank=100;
 ?column?
----------
        1
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        8 |            6 |             6
(1 row)

idx_tup_read is increased whenever an index entry is read.
idx_tup_fetch is increased whenever an index scan fetches a table entry.
If your index-only scan increases idx_tup_fetch, it is not truly index-only. PostgreSQL had to fetch the table rows anyway just to check if they are visible or not, an information that is not stored in the index.
You can see that as "heap tuples fetched" in EXPLAIN (ANALYZE) output.
The solution is to VACUUM the table so that the visibility map gets updated.


Exclusion constraint for text values I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table.
My constraint is that a vehicle cannot belong to a client if it already is associated with another client.
This is what I have so far:
ALTER TABLE unit
   ADD CONSTRAINT unit_vehicle_across_client_constraint
   EXCLUDE USING GIST (client WITH =, vehicle WITH <>) DEFERRABLE;

But it doesn't seem to work. I guess I just don't really understand how the exclusion parameters really work.
Any suggestions?

As suggested a simple UNIQUE constraint on vehicle does the trick. Not sure why I wanted to do complicated.


Determine databases specified Login has access to on SQL Azure I need to obtain a list of all the databases a specific user has access to. To this end I am using the following ...

USE master;
GO
EXECUTE AS LOGIN = N'Login-I-Want-To-Check';
GO
SELECT name ,database_id
  FROM sys.databases
  WHERE database_id > 3 -- Eliminates system tables
  AND HAS_DBACCESS(name) = 1
  ORDER BY name ASC
GO
REVERT; -- Switches context back to caller of this script

This works great on my DEV SQL Server but not on SQL Azure where I get this error ...

Msg 40517, Level 16, State 1, Line 3
Keyword or statement option 'login' is not supported in this version of SQL Server.

Does anyone know how to work around this so I can determine the databases a specified user has access to on Azure?

-- Run this query on master

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.*
FROM sys.sysusers A
     FULL OUTER JOIN sys.sql_logins B
     ON A.sid = B.sid
WHERE islogin = 1 and A.sid is not null

-- Run this on user databases

SELECT DB_NAME(DB_ID()) as DatabaseName, *
FROM sys.sysusers


How to create a trigger in oracle to prevent overlapping date ranges I'm learning Oracle and I had a problem.

Chat table:

CREATE TABLE training
(
   id_training      NUMBER,
   id_user          NUMBER,
   start_training   DATE,
   end_training     DATE
);

I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.

I was thinking of using :old and :new in the trigger, but if I use an insert in my trigger the data of :old always will be null.

How can I overcome this problem of changing :old values, if I'm inserting new values inside my trigger?

Here's a sketch. I get an error from the trigger on db<>fiddle, but you shpould be able to use the idea. First I'll add some constraints on the table, this will simplify things a lot:

CREATE TABLE training
( id_user          NUMBER NOT NULL
, start_training   DATE NOT NULL
, end_training     DATE
,     constraint ak1_training unique (id_user, start_training)
,     constraint c1_training check (start_training <= end_training)
);

I assume id_training is some sort of auto increment number so I left that out since it is not relevant for the idea. Comments:

All attributes except end_training is not null
is_user and start_training is unique
start_training is before or equal to end_training. The constraint is satisfied if end_training is null.

.

CREATE OR REPLACE TRIGGER training_validation
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
    IF EXISTS (
           select 1 from training t
                 where t.id_user = new.user
             and (t.start_training between new.start_training and coalesce(new.end_training, current_date))
                or
                  coalesce(t.start_end, current_date) between new.start_training and coalesce(new.end_training, current_date))
     )

  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Some msg ...' );
  END IF;
END training_validation;

I get the error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.

FWIW, I get the same error with the trivial examples I found googling around

Anyhow, the idea is to throw an error if there exists a previous start_training between the new.start_training and new.end_training. Since the new.end_training is typically null, we map that to current_date via coalesce. In the same manner, we check for the previous start_training.

Note that if start_training is not null, we won't have to take possible nulls in consideration. Also since we know that:

start_training <= end_training

we can rely on that as well.


MySQL Backups - Full vs incremental - innobackupex So I'm implementing a backup strategy for our project and came across some text which I need absolute clarification, false positives are the death nell of IT.

I am looking to do your typical backup strategy, weekly full back-up with daily incremental backups. Here's the rub:

According to the docs, when doing a full backup you have to 'prep' it, which is to run all uncommitted transactions etc. You can prep the backup by using the innobackupex --apply-log /path/to/BACKUP-DIR command.

But according to these docs, if you do an incremental backup, you have to 'prep' it differently, using the innobackupex --apply-log --redo-only BASE-DIR command.

My plan is to write a cronjob to do a full backup on Sunday @ 12:01am, and an incremental on M-Sa @ 12:01 am. So my question is, should the cronjob prep every backup with the --redo-only flag, to ensure that full backup can be merged with following incremental backups?

No, you don't need to prep every backup. You only need to create the full backup on Sunday and then create the incrementals from Monday to Saturday.

Full:

innobackupex --user=USER --password=PASSWORD /path/to/backup/dir/

Incr:

innobackupex --incremental /path/to/inc/mon --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

innobackupex --incremental /path/to/inc/tue --incremental-basedir=/path/to/inc/mon --user=USER --password=PASSWORD

The prepare phase (--apply-log) is only needed when you restore the backup.


Can two servers access the same database files? I'm designing an application and considering some options regarding the database system since I'm not familiar with Microsoft SQL Server. I would like to know if it's possible to have 2 servers sharing the same files, as shown below.

The idea is that the data is in a raid system so it would grant us some safety. This way we would save effort in synchronizing both databases and save some money on storage. We only need one database at a time, the secondary is just for use in case of failure of the first.

Is this possible?

I'm open to different approaches. The main problem is the database redundancy, our application must guarantee that.

At the moment I'm using Windows 7 and SQL Server 2008R2.

It is possible for two separate servers to simultaneously access a shared read-only database. The feature is known as Scalable Shared Databases.

From your description, it sounds more like you need a failover cluster, not a shared read-only copy.


Process attempted to unlock a resource it does not own SQL Server 2005 SP4 32-Bit

I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean when I run it now. The database is fully accessible. Should I be concerned? I'd hate to go back to a backup at this point.

 2013-04-02 02:10:55.53 spid56      Error: 1203, Severity: 20, State: 1.
 2013-04-02 02:10:55.53 spid56      Process ID 56 attempted to unlock a resource it
                                    does not own: PAGE: 34:1:388664. Retry the
                                    transaction, because this error may be caused
                                    by a timing condition. If the problem persists,
                                    contact the database administrator.
 2013-04-02 02:10:55.58 spid56      Error: 3314, Severity: 17, State: 3.
 2013-04-02 02:10:55.58 spid56      During undoing of a logged operation in
                                    database 'MY_DATABASE_NAME', an error occurred
                                    at log record ID (1342973:12519:37). Typically,
                                    the specific failure is logged previously as
                                    an error in the Windows Event Log service.
                                    Restore the database or file from a backup,
                                    or repair the database.

If this is just one time issue and CHECKDB run without any errors and you have good backups, do not bother to investigate it (unless you want to spend time and resources to what is not reproducible).

If you are able to reproduce the issue, then I would recommend you to

Replicate on a test environment and capture whats going using a
Profiler.
As a proactive measure, you can probably have an agent job
or powershell job to scan the error log (note that you can put a logic inside Powershell job to only scan from the last time it ended) to look for specific errors like Error: 1203 or Error: 3314 and then fire SQLDIAG to capture what was actually going on when the issue happened.

Let me know if you need any such script and I will be happy to share it.


Oracle 11.2.0.4 -- "sofar" increasing slowly in V$SESSION_LONGOPS I am running a long-running query, and it seems to be "stuck" on this operation here:

http://i.stack.imgur.com/MPnFB.png

From v$session_longops, the value of "SOFAR" seems to be increasing at 2-3 blocks per minute on this sort operation. The "time remaining" is misleading as it has been stuck at 34 seconds. I don't see any other contention in the database that may be causing it.

I would appreciate some pointers as to where I could look next. I don't see any abnormal level of wait events.
Thanks

The TIME_REMAINING in V$SESSION_LONGOPS is much like the time remaining on downloads in Internet Explorer or file copies in Windows: It is a guess, massively subject to change, and often not a very good guess. I use SOFAR divided by TOTALWORK to measure progress. As long as SOFAR is moving forward at the expected speed, there is no problem. e.g.

select opname, sid, serial#, sofar, TOTALWORK,START_TIME,round(sofar*100/TOTALWORK,2) pct_complete,
LAST_UPDATE_TIME , round(time_remaining/60) as "Minutes Remaining"
from v$session_longops
where sofar <> totalwork and totalwork > 0
order by START_TIME;


SSIS: Working with 3 constrains where 2 should be OR and one should be AND So I have an SSIS flow in which I am working with three constraint within a task, but 2 of them must be an OR and one must be an AND. In pseudo-code would be something like:

if ( (constraint-a OR constraint-b) AND constraint-c)

Problem is, we can only set OR/AND to all precedents:

Are there any solutions in which we could group some of these constraints?

Here is my original solution for this problem, which apparently is not as bad as I thought. The only problem with it is that the designer canvas can get quite crowded.

I managed to achieve the desired solution with a little kludge, which gives me exactly what I want. However I can't help but feel a little dirty doing it, so I wonder if there is a better way. Here is a diagram of what I have done:

NOTE: It is important to emphasise that I need Main Script B to start as soon as Main Script A completes.

Script Task B1 is the one requiring the complex constraint. As you can see in the centre of the diagram, I grouped failure of Main Script A and completion of Script Task A2 in an empty container.

This allows for script tasks A1 and A2 to be bypassed if Main Script A fails, and Script Task B1 to be executed after previous script tasks have been completed. But Script Task B1 must wait for Main Script B to finish as well.

Here are the results using this cheap kludge:

When Main Script A succeeds:

When Main Script A fails:

I wonder if there are alternatives to this solution, though.


DB2 huge IREF and lots of scans I received a task from one of my colleagues to check why he always receives high CPU load alerts.
I don't have too much experience with databases and I need some clarifications.
I created a script to capture the first 5 threads that are using most of the CPU when the alerts are triggered.
The order is almost always the same, first three are db2agents and the other 2 are db2pfchr.
From my knowledge, I found nothing wrong with the SQL statements so I checked for table scans and IREF.

What I noticed immediately was the difference between rows read and rows selected which looks like this for example:

Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 1329
Rows selected                              = 714
Rows read                                  = 3903430587518
Rows written                               = 3471

First 3 biggest table scan values are 26586532, 15538513 and 942177.
The first table has one index that was never used, the second table has 1 index that was used around 70k times and the third table has no index.

So, my obvious guess is that indexes need to be added in order to avoid table scans

Since I never added an index in my life and must read more about it, I needed to be sure about my assumptions and maybe there something else to check?
Also, all values in the queries are "?"... hidden?
Like UPDATE X  SET VALUE_INT = ?,  VALUE_STRING = ?,  Y = CURRENT TIMESTAMP  WHERE AB = ?

This is an OLTP DB, and it's about DB2 V10.5 on RHEL
IREF - Index Read Efficiency = ROWS READ/ROWS RETURNED

The ?'s are Parameter markers. Read abut them here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0020295.html

I would suggest you add an index on column AB

Also consider using IBM Data Server Manager to monitor you database. It includes the ability to see explain plans and also invoke the design advisor


Drop user in redshift which has privilege on some object I have a pretty standard problem that I can't resolve. I want to remove a user in redshift DROP USER u_A; which returns me: user "u_A" cannot be dropped because the user has a privilege on some object.

The problem is that I have no idea what kind of privilege is this and on what object.

In PostgreSQL I would just REASSIGN OWNED BY u_A TO u_B where a u_B is some other user. The problem is that redshift does not support REASSIGN. The amount of tables and schemas I have is too big (so I can't randomly try everything hoping that at some point I will remove the needed privilege.

So how can I remove that user?

There are some queries that give you the privileges currently granted to users on any objects:

Schema ACL

select
    nspname                      as schemaname
  , array_to_string(nspacl, ',') as acls
from
    pg_namespace
where
    nspacl is not null
and nspowner != 1
and array_to_string(nspacl, ',') like '%u_A=%' -- REPLACE USERNAME
;

Table ACL

select
    pg_namespace.nspname as schemaname
  , pg_class.relname as tablename
  , array_to_string(pg_class.relacl, ',') as acls
from pg_class
left join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where
    pg_class.relacl is not null
and pg_namespace.nspname not in (
    'pg_catalog'
  , 'pg_toast'
  , 'information_schema'
)
and array_to_string(pg_class.relacl, ',') like '%u_A=%' -- REPLACE USERNAME
order by
    pg_namespace.nspname
  , pg_class.relname
;

Privilege Explanation

      r -- SELECT ("read")
      w -- UPDATE ("write")
      a -- INSERT ("append")
      d -- DELETE
      D -- TRUNCATE
      x -- REFERENCES
      t -- TRIGGER
      X -- EXECUTE
      U -- USAGE
      C -- CREATE
      c -- CONNECT
      T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
      * -- grant option for preceding privilege

  /yyyy -- role that granted this privilege


How does SQL Server calculate the initial size of a (compressed) backup? When a backup is created, SQL Server guesses(?) the a size of the intial backup file. Later on, maybe when it appends the log the size will be re-adjusted, sometimes multiple times until the final size is reached.
The bigger the difference, the longer the backup takes. (compared to another backup which size isn't adjusted that much later on).
Example:
Database1 (size 500GB, 70 GB log used) is backed up with compression.
The .bak file is created with 85 GB in size, after some time the CPU usage goes up and I can see that the .bak file is re-adjusted to 136 GB, this happens again until the final size of 178GB for the backup is reached.

When these re-calculations happens the average backup speed in MB/s is reduced compared to other backups on the same machine. Is the only coming from appending and clearing the log? Or is it also because of the different data types used in the database? Meaning that they are compressed with different compression rates, or anything?

I came to the topic, because I knew that my backups take around 30 minutes, but now it took 1,5 hours even though the database didnt grow to 300% of it size. It grew only 30%.

Using wait stats I could see that besides BackupIO I was also waiting for CPU (SOS_SCHEDULER_YIELD) at some point.

Machine Details:
VMWare 6.0
32 GB of Memory (Max memory 28GB given to SQL Server)
2 logical CPU
Max Degree of Parallelism (1, it's a Sharepoint 2013)
SQL Server 2014
Windows Server 2012 R2
running on an SSD Raid (5)

Of course I have instant file initialization enabled for the user who runs the backup.

How does SQL Server calculate the initial size of a (compressed) backup?

From BOL :

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.
To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm.

You should follow - Backup and restore best practices in SharePoint 2013.
Also, to me your VM having 2 CPU seems to be underspec for running a 70GB database (Not sure if there is only one database or multiple databases).


MariaDB Cluster - data consistency for updates and auto_increment I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and have some questions. And I not use transactions, foreign keys, etc...

1) If I understand it well - unlike of MySQL Cluster on Galera I do not have to worry about auto increment values. Config auto_increment_offset is not needed with Galera. Is this true?

2) I read many articles and discussions about data consistency and replication but still have questions. If application not use transaction and for example run 10x query "UPDATE a SET b=b+1 WHERE c=1 LIMIT 1". Can I be 100% sure that "SELECT b FROM a WHERE c=1 LIMIT 1" return right value? On one slideshare presentation I read - "Data does not belong to a node - Node belong to data". So, it's true that Galera replicator take all care about consistency and no changes in application is needed?

1 - Yes, Galera takes care of auto increments for you, but you will get lots of missed ids, so make sure your fields are big enough (e.g. you might need a smallint somewhere whereas without missing ids you could have got away with a tinyint).

2 - Yes, no changes should be needed to the application, except for occasionally you might get deadlocks. You should come up with a system to check error messages automatically and if you get errors like Deadlock found when trying to get lock; try restarting transaction and Lock wait timeout exceeded; try restarting transaction then redo the query and it should go through ok. The data will remain consistent. There is also a Galera setting for automatic retries, but we found it was better to try them again yourself. Obviously you shouldn't be using mysql_query() at all, but for the purposes of a simple to follow example, you could swap your queries to use something like this instead of your usual query function

function vio_mysql_query_deadlock_safe($query, $db = null) {
    $deadlock_retries_done = 0;
    $deadlock_retries_max = 10;
    while ($deadlock_retries_done < $deadlock_retries_max) {
        if (is_null($db)) {
            $q = mysql_query($query);
        } else {
            $q = mysql_query($query, $db);
        }
        if ($q === false) {
            $error = mysql_error();
            if ($error == 'Deadlock found when trying to get lock; try restarting transaction' || $error == 'Lock wait timeout exceeded; try restarting transaction') {
                $deadlock_retries_done++;
                continue;
            } else {
                throw new Exception($error . '. Query: ' . $query);
            }
        }
        return $q;
    }
    throw new SQLException($error . '. Re-tried with deadlock ' . $deadlock_retries_done . ' times. Query: ' . $query);
}


SSAS Cube Only Displays Total Values When browsing my cube it shows the correct invoice values by date when I select only a date type (the date table was generated by SSAS with hierarchies), but if I add the customer name it shows the combined value of the invoices for the date period, rather than the total per customer. I expect to see the total value for the customer that month, not the overall monthly total.

My tables are joined with foreign keys and the DSV shows a star schema. A simplified version of the schema is below.

factInvoice
invoicePK
customerFK -> customerPK
dateFK -> datePK

dimDate
datePK

dimCustomer
customerPK

The dimension usage tab shows both dimensions are linked to the invoice table.

Please can somebody shed some light on why this is happening? I'm sure I'm missing something pretty basic here! Thanks.

The Customer dimension is joined to the measure group at a non-key granularity. If the Customer Name attribute isn't related to the attribute you join on then data repeats in reports. You need to do one of two things:

Recommended. Change the fact table to have the surrogate key of the dimension. Then change the Dimension Usage tab to join to the key of the dimension.
Change the dimension to change which column is used as the key of the dimension. Make sure the fact table has this column. Fix the Dimension Usage tab to join to the dimension key.


SQL Server consuming all my CPU I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost 100% when geoserver executes following queries :

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR)
  + ':' + "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "LiveData"."sites"
         WHERE ("SP_GEOMETRY".Filter(
    geometry::STGeomFromText('POLYGON
    ((87.16003722182671 27.034666490477207,
      87.16003722182671 28.328562656235395,
      88.62121886224784 28.328562656235395,
      88.62121886224784 27.034666490477207,
      87.16003722182671 27.034666490477207))', 4326)) = 1
AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))

--

SELECT "Site_ID","Vendor",CAST("sp_geometry".STSrid as VARCHAR)
  + ':' + "sp_geometry".STAsText() as "sp_geometry"
FROM "LiveData"."sites"
WHERE ("sp_geometry".Filter(geometry::STGeomFromText('POLYGON ((
  82.89734190991959 26.99551618775358,
  82.89734190991959 28.367238076413702,
  84.44641417532948 28.367238076413702,
  84.44641417532948 26.99551618775358,
  82.89734190991959 26.99551618775358))', 4326)) = 1
AND ( UPPER("Vendor") LIKE 'A%'  OR
 UPPER("Vendor") LIKE 'B%' ))

Now the problem is as I open multiple pages just 4-5 pages the web pages hangs as it has to retrieve data from database.

I have a view that is made spatial by selecting spatial column SP_GEOMETRY from  base table:

    CREATE view [LiveData].[sites]
      As
     select
     live.Site_ID as siteId,live.Site_Name,live.Vendor,live.Status,live.MI_SQL_REC_NUM,
     live.MI_STYLE,live.MI_PRINX,live.SP_GEOMETRY
     from baseTble.spatialSites live ,LiveData.liveStatus stat
where
live.Site_ID  = stat.siteId and stat.Code in ('x123','y345') and stat.alias = 'Neyman'

I haven't worked much with geospatial types but the fact that it's CPU-intensive isn't all that surprising. If you want to avoid the CPU pegging out at 100% for the server I'd suggest taking a look at your MAXDOP settings at the instance level. The default value is 0 meaning SQL Server will use up all available processors (logical) when processing. If your query is pegging out all your CPUs and causing issue you may want to experiment tweaking your settings. Take a read of @paulrandal's post on MAXDOP: http://www.sqlskills.com/BLOGS/PAUL/post/MAXDOP-configuration-survey-results.aspx

Word of caution: Don't go changing this willy-nilly in production as it WILL affect performance and the last thing you want is to get yelled at/fired because "some guy on internet said it'd work!". Test your settings in development environment first and see if this works for you.


Should Full Text Filter Daemon Service be a cluster resource? Our newly installed and configured SQL Server Always On Availability Failover Cluster with SQL Server 2016 Enterprise Edition runs with four instances and six nodes. It includes the DB Engine Service and the Agent Service in each Cluster Node's resources. Everything works fine.
But now we figured that the fill-text Filter Daemon Launcher (FTFDL) service exists on each of the nodes and is randomly running, independant if the fact where the Sql-Instance is currently running. Some nodes have four FTFDL services running, while only one instance is located there. Others have one or two of them running.
Question: Should we add this service as a cluster resource? Why is it getting started and stopped abviously randomly on the failover cluster instances resutling in multiple services active without the relevant instance active? What is best practice here?

The Full-Text-Search daemon launches as required, and is triggered by the SQL Server engine itself.
So, in short, you don't need to add the Full Text Filter Daemon Launcher as a resource in a SQL Server failover cluster.
From the Docs page concerning Service Account settings for the Full Text Filter Daemon Launcher:

Make sure the SQL Server service group has permissions to the SQL Full-text Filter Daemon Launcher service account. During the installation of SQL Server, the SQL Server service group is granted default permission to manage, query, and start the SQL Full-text Filter Daemon Launcher service. If SQL Server service group permissions to the SQL Full-text Filter Daemon Launcher service account have been removed after SQL Server installation, the SQL Full-text Filter Daemon Launcher service will not start, and full-text search will be disabled.


configure sqlcmd to automatically run your script every time it starts? I have created a sqlcmd startup script that i have stored in c:\myscript.sql, and I want it to run every time i start sqlcmd.
     how do I configure sqlcmd to automatically run your script every time it starts?

You will need to set the SQLCMDINI variable in Windows.  For example:

C:\SET SQLCMDINI=c:\sqlscripts\init.sql

The init.sql (or whatever you name it) would contain the initial script.


How should I store a large number of dynamically generated heterogeneous tables? I'm a developer on a reporting application that runs complex user-defined queries, stores the results, and then sends the result to Excel generation, web-based tables, and graphs, and so on. Currently, we're storing the entire result set serialized as a clob in an existing table.

These result sets may be large (some >1GB) and expensive to generate (many minutes). Regenerating them on-the-fly is not an option. They also are heterogeneous -- each result set will likely have very different columns. The result sets are immutable -- they will never be modified, although they will eventually be deleted. Each result set will be read occasionally, but often in bursts (e.g. if someone views the data via our web interface, then we expect many more requests soon).

Currently, these thousands of result sets are taking up a lot of DB space. In addition, it is difficult to manipulate the values other than to select the entire result set or the first n records. We would like to be able to sort and filter the result set. It seems logical that if we have tabular data that we'd like to sort and filter, then storing it as a table is the obvious answer.

However, I'm not sure what the right way would be to do this in Oracle. The naive solution would be to dynamically run CREATE TABLE statements for every one, naming them from a GUID or something. This kind of vaguely feels like an unwise thing to do, maybe unless we also move this data to a separate database. I looked up global temporary tables, but we need them to persist across sessions. A co-worker suggested using external tables, but I'm not sure I understand the performance implications of that. Basically, I'm in over my head from an Oracle architecture standpoint, and I'd appreciate advice from more experienced developers and DBAs.

So: given these constraints, what approaches should I consider?

You could use a data-defined schema, which isn't terrific for performance or manageability but comes closer to doing what the database was designed to do.

CREATE TABLE report_result_tab (
   report_result_id NUMBER NOT NULL,
   row_id           NUMBER NOT NULL,
   column_name      VARCHAR2(30) NOT NULL,
   value_number     NUMBER,
   value_varchar    VARCHAR2(4000));

ALTER TABLE report_result_tab
   ADD CONSTRAINT report_result_pk
   PRIMARY KEY (report_result_id, row_id, column_name);

With a table like that, you get a variable number of "columns" by putting the column as part of the key.

Let's say we're reporting on a table like this:

CREATE TABLE employee (
   employee_id NUMBER,
   last_name   VARCHAR2(255));

Then to report on it, with one report having two records, it would look like this:

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   0,             /* row_id */
   'EMPLOYEE_ID', /* column_name */
   37,            /* value_number */
   NULL);         /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   0,             /* row_id */
   'LAST_NAME',   /* column_name */
   NULL,          /* value_number */
   'Smith');      /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   1,             /* row_id */
   'EMPLOYEE_ID', /* column_name */
   42,            /* value_number */
   NULL);         /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   1,             /* row_id */
   'LAST_NAME',   /* column_name */
   NULL,          /* value_number */
   'Jones');      /* value_varchar */

You'll have to figure out what to put as the COLUMN_NAME in the base data. You could use a numeric COLUMN_ID which would save space. In either case, you'll also have to figure out a mapping to get the user-friendly column names and their sort order.

The method above is advantageous for sparse data, but keeping so many copies of the column IDs is expensive.

I've also seen a much uglier version which could potentially be cheaper on disk space. You could define a particular report type, which is nothing more than a collection of column metadata, then build your table to handle the maximum number of columns of each type.

CREATE TABLE report_result_tab (
   report_result_id NUMBER NOT NULL,
   row_id           NUMBER NOT NULL,
   n00              NUMBER,
   n01              NUMBER,
   n02              NUMBER,
   n03              NUMBER,
   n04              NUMBER,
   /* ... */
   s00              VARCHAR2(4000),
   s01              VARCHAR2(4000),
   s02              VARCHAR2(4000),
   s03              VARCHAR2(4000),
   s04              VARCHAR2(4000),
   /* ... */
   d00              DATE,
   d01              DATE,
   d02              DATE,
   d03              DATE,
   d04              DATE);

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   n00,
   n01,
   n02,
   n03,
   n04,
   /* ... */
   s00,
   s01,
   s02,
   s03,
   s04,
   /* ... */
   d00,
   d01,
   d02,
   d03,
   d04)
VALUES (
   0,        /* report_result_id */
   0,        /* row_id */
   37,       /* n00 */
   NULL,     /* n01 */
   NULL,     /* n02 */
   NULL,     /* n03 */
   NULL,     /* n04 */
   /* ... */
   'Smith',  /* s00 */
   NULL,     /* s01 */
   NULL,     /* s02 */
   NULL,     /* s03 */
   NULL,     /* s04 */
   /* ... */
   NULL,     /* d00 */
   NULL,     /* d01 */
   NULL,     /* d02 */
   NULL,     /* d03 */
   NULL);    /* d04 */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   n00,
   n01,
   n02,
   n03,
   n04,
   /* ... */
   s00,
   s01,
   s02,
   s03,
   s04,
   /* ... */
   d00,
   d01,
   d02,
   d03,
   d04)
VALUES (
   0,        /* report_result_id */
   0,        /* row_id */
   42,       /* n00 */
   NULL,     /* n01 */
   NULL,     /* n02 */
   NULL,     /* n03 */
   NULL,     /* n04 */
   /* ... */
   'Jones',  /* s00 */
   NULL,     /* s01 */
   NULL,     /* s02 */
   NULL,     /* s03 */
   NULL,     /* s04 */
   /* ... */
   NULL,     /* d00 */
   NULL,     /* d01 */
   NULL,     /* d02 */
   NULL,     /* d03 */
   NULL);    /* d04 */


Three-sided many-to-many relationship design I have 3 entity that have a relation to each other. My entities are:

OPTION
PLAN
PRICE

Relationships:

PLAN and OPTION have a many-to-many relationship
Map of PLAN and OPTION has a many-to-many relation with PRICE

any plan has some options. an option of one plan has some price.
My possible solutions:

I can create an PLAN_OPTION_MAP table and then map it with PRICE and create PLAN_OPTION_MAP_PRICE_MAP table.

I can create an PLAN_OPTION_PRICE_MAP table with id of all 3 tables that primary key is made by composite of them

Which is the better solution?

I think the first solution is good when you know that some instances WOULD NOT participate in relation.
But what about second solution? I think it's good when you're sure that all of the instances WOULD participate in relation.
The first thing you can do, is analyzing your Problem.


monitoring of alert log file I have to generate automated script for alert log file, which will display the contents of alert log file. Here is the script for that.

while :
clear
do
echo "\n"
echo "ORA - Error"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"
sed -n "/ORA-/p" "alert log fiel path"
sleep 4
clear

My issue is that I am getting all the errors. I only want errors which occured today. How can I do this?

1) If you are using Oracle 11g or later:

Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does.
Here is a simple query that retrieves all errors which occured today, like you want.

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message

SELECT record_id, message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - 1 AND regexp_like(message_text, '(ORA-|error)')

As Twinkles mentioned in his answer, you can take advantage of ADRCI utility to select alert filters that you need (in fact, this utility queries against the X$DBGALERTEXT table). For example, you can build a simple function and call it from OS prompt:

vealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%ORA-%' \
     and originating_timestamp > systimestamp - $1\\\""
}

in file .bashrc, for example:

export DB_UNIQUE_NAME=<unique_name>
. $HOME/bin/vealert

Then:

bash
vealert 1

..
Tue Dec 24 17:46:05 2013
ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
..

2) Otherwise:

Go and have a look at this web page. There is a script that can be used to check any ORA- errors are there in the alertlog file for the present day.

To schedule this job, you can add a line in your cron table specifying the time you want the job to execute. Alternatively, you can take advantage of the Oracle Scheduler.


SQL Server 2005 Tools makes SQL Server 2008 Express install fail When I tried installing SQL Server 2008 Express, the install failed due to this line.  How can I circumvent this, or fix the issue?

Open Run Menu and type regedit.
It will open Registry Editor
Now go to following location.

HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90

Right click on 90 and click on Delete.
Press Yes to confirm deletion.

Now try to install SQL Server 2008 and it would work this time.


What is the advantages and disadvantages of maintaining one ORACLE_HOME? I am a little confused about why a DBA would want to maintain one ORACLE_HOME? I can see no advantages.

Having multiple homes would allow multiple versions of Oracle installed.

First of all if you want to have multiple oracle version (11g, 12c etc.) you must have multiple homes.

The advantage of one oracle home is less space consumed. Less work with patching one home.

But if you have more instances under this one home, you have to patch all databases (post patch sql) which is more work :-).

So it depends on how many versions and how many instances do you have on the server.


Nested Loop with Materialize resulting in very slow query on postgresql 9.5 I have an analytics database on Postgres 9.5
I'm taking advantage of constraint partitioning with table inheritance to split up events into monthly tables. The base (parent) table contains no rows.

I have a relatively simple query but the planner is coming up with a completely insane plan where it materializes within a nested loop as can be seen from the explain.

The query

select count(person_id) as thecount from (
    select distinct A.person_id from event_page as O
    join alias as A on (O.person_alias = A.alias)
    where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
    and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697

The explain: https://explain.depesz.com/s/IoO

My theory is that table inheritance with constraint partitioning is tripping up the planner somehow. If I replace the base table with the monthly table for April I get a sane plan with good performance:

https://explain.depesz.com/s/GpbU

NOTE The slight difference from the above query - i'm using the child table instead of the base table (ie no inheritance at play)

select count(person_id) as thecount from (
    select distinct A.person_id from event_page_2017_4 as O
    join alias as A on (O.person_alias = A.alias)
    where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
    and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697

All stats are up to date via vacuum analyze. The relevant schema is below:

CREATE TABLE event_page (
    id                      CHAR(24) PRIMARY KEY,
    timestamp_              TIMESTAMP NOT NULL,
    person_alias            VARCHAR(128) NOT NULL,
    visitor_id              VARCHAR(128) NOT NULL,
    session_id              VARCHAR(24) NOT NULL,
    ip_address              VARCHAR(64) ,
    user_agent              VARCHAR(256) ,
    operating_system        VARCHAR(64) ,
    device_type             VARCHAR(64) ,
    browser                 VARCHAR(64) ,
    browser_major           VARCHAR(64) ,
    page_title              VARCHAR(1024) ,
    location_host           VARCHAR(256) ,
    location_path           VARCHAR(1024) ,
    location_query          VARCHAR(1024) ,
    location_fragment       VARCHAR(1024) ,
    referrer_host           VARCHAR(256) ,
    referrer_path           VARCHAR(1024) ,
    referrer_query          VARCHAR(1024) ,
    referrer_fragment       VARCHAR(1024) ,
        duration                INT
);

--the monthly tables all look like this example for April 2017
CREATE TABLE event_page_2017_4
(LIKE event_page including defaults including constraints including indexes,
CONSTRAINT page_2017_4_part CHECK ( timestamp_ >= '2017-4-01'::timestamp AND timestamp_ < '2017-05-01'::timestamp )
)
INHERITS (event_page);

--indexes are the sames on each of the monthly tables
CREATE INDEX ep_2017_4_location
  ON event_page_2017_4
  USING btree
  (location_host varchar_pattern_ops, location_path varchar_pattern_ops, location_query varchar_pattern_ops, location_fragment varchar_pattern_ops, timestamp_, person_alias, session_id);

CREATE INDEX ep_2017_4_ts
  ON event_page_2017_4
  USING btree
  (timestamp_, person_alias, session_id);

CREATE INDEX ep_2017_4_a_ses_ts
  ON event_page_2017_4
  USING btree
  (person_alias, session_id, timestamp_);

--the alias tables and two indexes
CREATE TABLE alias (
    person_id               CHAR(24),
    alias                   VARCHAR(128) NOT NULL,
    first_seen              TIMESTAMP,
    soft                    BOOLEAN
);

ALTER TABLE ALIAS ADD CONSTRAINT alias_alias_pkey PRIMARY KEY(alias);
CREATE INDEX alias_a_p ON alias (alias, person_id);
CREATE INDEX alias_p_a ON alias (person_id, alias);

NOTE: The problem went away after upgrading to 9.6.1

A couple of observations:

1.

The query plan for the slow query shows:

  Seq Scan on event_page o (cost=10,000,000,000.00..10,000,000,000.00 rows=1 width=274)

... which indicates that you ran with SET enable_seqscan = off;. And that means your Postgres version positively could not find any other way than the sequential scan.

2.

Did you enable constraint_exclusion like the manual advises here:

  Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized
  as desired.

3.

The day boundaries in your query are not in sync with the partitioning. (But that has no immediate effect for the given query, see comments.)
The CHECK constraint reads:

CHECK ( timestamp_ >= '2017-4-01'::timestamp AND timestamp_ < '2017-05-01'::timestamp )

There may be confusion with timestamp vs. timestamptz and / or time zones. Or something got lost in translation and the question is misleading in that respect.

Either way, the expression in your WHERE clause opens up a sneaky corner case (even if your day boundaries were in sync):

where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'

Postgres timestamps are implemented as 8-byte integers allowing 6 decimal places. A row with '2017-4-29 03:59:59.9995' would not behave as expected.
Details:

Ignoring timezones altogether in Rails and PostgreSQL

Plus, this does not play well with constraint exclusion. More partitions than necessary may have to be read.

Use instead:

WHERE o.timestamp_ >= '2017-4-28 04:00' -- include lower bound
AND   o.timestamp_ <  '2017-4-29 04:00' -- exclude upper bound

You may want get "days" in your partitions in sync with your queries - 00:00 vs. 04:00 (or the evil 03:59:59.999) - and maybe use timestamptz to begin with.

4.

I see for the index-only scan on alias:

  Heap Fetches: 1918543

... which is exceptionally high. There may or may not be problems with the visibility map. (See also: Updating The Visibility Map) Did you run VACUUM FULL on the table(s)? (Compare this thread on plpgsql-performance.) Try plain VACUUM, which updates the visibility map properly. But that's all hard to say, since you seem to have been running with enable_seqscan = off. Not sure how this plays out in combination with all the other stuff at work here.

There have been various improvements to VACUUM for Postgres 9.6 (among other things), this may explain the difference - or what you observed in pg 9.6 is coincidence and due to other factors.

5.

If alias.alias is unique (and referential integrity can be assumed), the query can be simplified. Just count(DISTINCT o.person_alias) without even joining to table alias at all. Like:

SELECT count(DISTINCT o.person_alias) AS thecount
FROM   event_page o
WHERE  o.timestamp_ >= '2017-4-28 04:00' -- include lower bound
AND    o.timestamp_ <  '2017-4-29 04:00' -- exclude upper bound
AND    o.location_host = 'www.foo.com'
AND    o.location_path = '/ca/sale';

(Or use the subquery like you had it, may be faster than count(DISTINCT ...).)

If alias.alias is not unique, your query may be wrong / ambiguous.

Why do you have person_alias in table event_page and not person_id to begin with?

6.

You use the data type CHAR(24) as PK for your tables, which is almost certainly a bad choice. Consider a serial or bigserial column or maybe a uuid. Numeric types are smaller and faster and not burdened by collation rules, varying byte length, etc. Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
What is the overhead for varchar(n)?


How to simulate bad backup file with 'ultraedit' I want to simulate bad backup file with ultreadit, but I don't know how as I know nothing about ultreadit. Please help me with that.

I simply want to make the SSMS fail to read the pages of a bad file.

You don't need UltrEdit to destroy a backup file.
Actually, this really powerful text editor will allow you to nose around inside a backup file and even save it without rendering the file completely unusable ...

Not that you should, obviously.

Good old Notepad is far more capable of causing this sort of damage.

Just open [a copy of] a Backup file and save it again.  Mischief Managed.

Notepad's lack of acknowledgement of Operating Systems other than Windows - and the line endings that they use - will cause it to rewrite the file with extra characters that make its contents complete nonsense to Sql Server.


sp_executesql - What is wrong with my Parameter-string? I've stared myself blind at this. It started as a more complex procedure, but I've stripped it down to the bare bones, trying to make it run. This is the current code:

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

EXEC [sp_executesql] @sql, N'@num int', @num = 1;

It simply refuses. It's worth mentioning that if I run the code without the parameter-part (@sql being the sole parameter to the sp_executesql function) it runs fine. I've tried other syntax alternatives, such as

EXEC [sp_executesql] @sql, N'@num int', 1;

and

DECLARE @params NVarchar(1000) = '@num int';
EXEC [sp_executesql] @sql, @params, 1;

What is wrong with my parameter-specification?

Edit 1: Included error-message

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.

You seem to be expecting that the parameters will end up being treated as string interpolation arguments.

They won't.

One way of achieving something similar to what you want would be simply to parameterise the stored procedure

CREATE PROCEDURE dbo.Test
@num int
AS
BEGIN
    SELECT 1, @num;
END

Then when you execute it you supply the desired value.

If you definitely want the procedure to end up with @num replaced with a hardcoded 1 you can use.

DECLARE @num int = 1

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

SET @sql = REPLACE(@sql, '@num', @num)

EXEC sys.sp_executesql @sql -- or just EXEC(@sql)

Take care of SQL injection if you go this route.

Also you would need to be careful of parameter/place holder names and order of replacements, so if you had place holders @num and @number the @number one would need to be replaced first.


Full-text search with german umlauts I created a table using German (Germany) as the Full-text language, in the settings I tried setting accent sensitivity to both sensitive and insensitive. I, however, get following odd results.

The table contains the following entries:

Müller - Muller - Mueller
Luginbühl - Luginbuhl - Luginbuehl

Executing following search

SELECT * FROM dbo.Search WHERE CONTAINS(CNameCombined, 'FORMSOF (INFLECTIONAL,  "Mueller")')

I get these results: Müller, Muller, Mueller

Executing a similar search

SELECT * FROM dbo.Search WHERE CONTAINS(CNameCombined, 'FORMSOF (INFLECTIONAL,  "Luginbuehl")')

I get zero results.

Does anyone have an idea what the reason for this behavior might be? It seems to work for some words, but not for all of them.

I'm running a SQL Server 2012 on a Windows Server 2012.

I think all you need to know is listed here http://technet.microsoft.com/en-us/library/ms142509.aspx Look for the right languages in the index and the query parser (http://technet.microsoft.com/en-us/library/ms142583.aspx#Additional_Considerations).
...and check/post your params


archiver stuck after set log_archive_dest_2 to fast recovery area oracle All data guard expert,

I am currently trying to configure a primary database for creating physical standby database with an oracle 11g stand alone database.

Oracle version: 11.2.0.4

It's on archivelog mode with automatic archiving using fast recovery area.

db_recovery_file_dest is set to /u01/app/oracle/oraarch

db_recovery_file_deset_size is set to 4G.

archiving is working normally before starting configure this database for physical standby database creation.

here is what's done so far on this primary database:

force logging on primary database is turned on:

alter database force logging;
standby redo logs are created. While this primary database has two redo log file groups, so I created 3 standby redo log file as Oracle recommeneded.

SQL> SELECT * FROM V$STANDBY_LOG;

GROUP# DBID THREAD#  SEQUENCE#

BYTES BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM

NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME

4 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

5 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

6 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

then

Set archive_log_dest_2 parameter to us DB_RECOVERY_AREA.

alter system set log_archive_dest_2 = 'location=user_db_recovery_file_dest valid_for=(standby_logfile,standby_role)'
scope=both;

and turn log_archive_deset_state_2 be 'enable', while it's was set to enable as default.

alter system set log_archive_dest_state_2=enable scope=both;

Then the problem I am stuck begins. When I issue SQL> alter system switch logfile; 3 times, then it's hung up.

I check the /oraarch directory, the redo log file are not even archived at the first two switch logfile command.

and when the third time switch logfile is issued, then it's hung up.

Please help/advise while you expertise is really appreciated. thank you so much.

regards

So you told the primary database, that it should put archivelogs in the FRA, but only when it has the standby role. After forcing log switches a few times, your repeated attempt of forcing a log switch hung, because with your settings, you basically prevented the database from archiving logs, and the database can not switch to a new log file group, because it could not archive any other previous log sequences, so it can not reuse the files.

Here is an example of the parameters needed in the primary database from the documentation:

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1 is a local destination that can archive any log in any role.

LOG_ARCHIVE_DEST_2 is a remote destination that points to the standby.

You should have something like this:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'

Also, here is the whole process of Creating a Physical Standby.


Grouping SQL Server Connections By Connection Pool In SQL Server 2014 is there a way to group connections by the connection pool that belong to? We are trying to find out if a specific application is leaking connections and hitting the default max pool size of 100.

In this doc on Connection Pooling it states that each pool is defined by the exact connection string used to establish the connection. However, I am unable to find anything in the sys table that allows me to see that.

The closest I have groups by loginname and program_name:

select  rtrim(p.loginame)
    ,p.program_name
    ,count(*)
from    sys.sysprocesses p
group by p.loginame, p.program_name
order by count(*) desc

Thanks in advance!

You can get a little closer if you add host process id but I don't think you'll be able to get an actual connection pool count 100% from server data because many connection string attributes are processed on the client and never sent to the server. This might be close enough for your needs, though.

SELECT
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id
    , COUNT(*) AS connection_count
FROM sys.dm_exec_sessions AS s
GROUP BY
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id;

A client-side exception like "timeout period elapsed prior to obtaining a connection from the pool" should be raised when the pool size is reached an another connection cannot be acquired within the connection timeout limit.


Recommended Postgres settings to speed up indexing on a 100GB database? I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk.

I will be using this server to serve both my site and my database (i.e. it's not just a database server). The database is 500m rows, approximately 120GB of data.

Once the data is loaded, the database needs to be optimised for SELECT queries - the data is fairly static in nature, and only changes once every three months. 99% of the queries made to the database will be reads.

I'm wondering what would be good Postgres settings to start out with. I've set:

effective_cache_size 8GB
shared_buffers 4GB

in the Postgres config file, but I haven't touched any of the other defaults.

With the settings above, I'm finding it very slow (more than an hour and still running) to create an index on one field in my table, and I want to create many of them.

Are there other settings I could look at to make index creation faster? Or can I safely increase these settings?

If I were setting up a new server, I wouldn't use anything below 9.3.6.  There have been many improvements since 9.1.

And if you use 9.4.1, there is some code to let it use larger amounts of RAM for index creation than it previously could.

Neither of the parameters you listed are particularly important for creating indexes.  The most critical for that purpose would be maintenance_work_mem.    On a 32GB server, if I'm only building one index at a time and keeping a close eye on things, I'd set it to 16GB for the local session that is building the large index.

Also, the C collation is much faster than the other collations, if you have a choice.

Is the bottleneck CPU or IO?


Want to perform a function on a returned table on subset of rows I have a query that takes 10 minutes to run (1.4 million rows and 15-ish joins), so I think the time isn't terrible.

What I need from this is to effectively INNER JOIN on itself an additional column based on a key of 'n' other columns. This key would be selecting 3 specific rows and performing a mathematical function to produce one result. This result would be joined onto the 3 rows in the final column:

I know I could (and have with smaller queries) just run the query again with a group by and join it on to the original, but this (I believe) would double the query time.

Is there a standard way to do this that I am just not aware of?

Completely stumped by this one, and have had to resort to asking a question on here (after searching). Bear with me as I am self taught!

Below is an example of how you might use the FIRST_VALUE and LAST_VALUE Windows Functions to calculate your Gain_loss column and then use a CASE expression to give you a Classification

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table
    (Col1, Col2, Col3, [Year], [Year+n], Result)
    values
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20),
        ('A', 'UK', '1Z', 2015, 0, 10),
        ('A', 'UK', '1Y', 2015, 2, 30),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)

--Use the FIRST_VALUE and LAST_VALUE Window Functions to calculate the Gain_loss
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
    FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n])
    AS Gain_loss
    ,CASE
        WHEN
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n])  > 0 THEN 'Up'
        WHEN
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                                ORDER BY Col1, Col2, Col3, [Year], [Year+n]) < 0 THEN 'Down'
        ELSE 'NoChange'
    END AS Classification
FROM @Table
ORDER BY Col1, Col2, Col3 DESC, [YEAR], [Year+n]

That produces the following results (which appears to match your desired results)

| Col1 | Col2 | Col3 | YEAR | Year+n | Result | Gain_loss | Classification |
|------|------|------|------|--------|--------|-----------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 20        | Up             |
| A    | UK   | 1Y   | 2015 | 0      | 40     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 1      | 35     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 2      | 30     | -10       | Down           |

To learn more about SQL Server Window Functions, check out:

SELECT - OVER Clause (Transact-SQL)

How to Use Microsoft SQL Server 2012's Window Functions


Drop database locked the server I tried to drop one of the databases on the server. That locked the entire server up so I stopped the mysql process via the console.

I restarted the server, started mysql and now it seems hanged on:

debian-sys-maint | localhost           |                 | Query   | 192  | Opening tables | select count(*) into @discard from information_schema.COLUMNS |

Not necessarily hanged,  but this takes a long time to move to a different query.

The other databases seem locked but I see no locks in "show open tables".

Any idea what's going on and what I could try?

Thanks.

Edit: it seems like the locks are moving from database to database and table to table. I guess it's doing some kind of checks?

Edit2: I think I understand what's happening, the answer to why it's doing this is: https://stackoverflow.com/questions/12567122/linux-mint-trigger-slowly-query-on-mysql-on-system-booting

Your conclusion is correct about the debian-sys-maint process.  That's not technically part of MySQL and can be disabled.

Your DROP DATABASE gave the appearance of a lockup because opening/closing/dropping tables uses a global mutex in MySQL -- only one such operation can be happening at any point in time and every other similar operation has to wait.  That wouldn't really be a problem, except that MySQL has to wait for the filesystem to report that the underlying table file(s) has been deleted, which can take a significant amount of time.

Dropping the tables individually won't eliminate the problem, but will spread out multiple, shorter blocking events over more time and be potentially less disruptive... or you might be able to use a different filesystem if dropping big tables is a frequent requirement for your environment:

http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/
https://stackoverflow.com/questions/150058/mysql-drop-database-takes-time-why


SQLDIAG File in the SQL log folder There were few inconsistency issues in our database and this happened because of unsuccessful fail-over of DB server(WSFC), same was asked in this question and is resolved last night after dropping and recreating primary key and moving data to another table.

There were many SQLDump files created before consistency error was fixed, its not getting created anymore however I see few SQLDIAG files in the log folder, opened the same using SSMS and it is set of events with name and timestamp, somewhere state_desc is appearing as clean whereas somewhere unknown or warning.

Can someone please help me understanding this and if its an issue which needs to be resolved?

  Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0
  (X64)   Sep  7 2018 01:37:51   Copyright (c) Microsoft Corporation
  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
   (Build 9600: ) (Hypervisor)

This is the output of sp_server_diagnostics which is run to do health checking of either the primary replica in an availability group or an FCI. These are normal files to have and can help when investigating issues with availability groups or FCIs when dealing with failover.

Depending on the component and state, it may be an issue or may not be an issue.

Clean - No issues
Warning - There have been some key metrics above what is expected
Error - When a policy has been violated and a failure should be taken

Only System, Resource, and Query Processing are involved in the failover policies. Events will always say unknown and io_subsystem will only ever be clean or warning.
You can read more about this here for AG or FCI.

Whether or not any of the items are an issue is another matter which can only be ascertained by the frame of which the issue occurred. Obviously if any say Error, that's a problem, but warning requires a frame of reference to understand if it's something that should be further looked into or disregarded.


How to improve SELECT performance on a big ORACLE view? I cannot use a materialized view for the base table data changed frequently. There are about 0.6 million rows in the_big_view. The query I need to do is: SELECT * FROM the_big_view WHERE student_number='*****', which returns arbitrary rows of data.

If I add an "ORDER BY student_number" clause in the "CREATE VIEW the_big_view AS ...", does that do good?

No, an order by in the view will not help. You may want to do two things. First of all you should ensure that student_number is indexed in the underlying table. Plus if the big view joins multiple tables you should ensure that the join columns are indexed.

Second you may want to use a number like this:

WHERE student_number=123456

instead of

WHERE student_number='123456'

if student_number is numeric to avoid type conversions.


MongoDB 4.2 using change streams with read concern majority disabled in a PSA deployment I currently run a PSA (Primary, Secondary, Arbiter) deployment and thous only have two databearing nodes.
In case of a databearing node going down, read concern majority prevented the change stream to be read out of.
In MongoDB 4.2 it is now possible to disable the read concern majority for the replicaset and still use the change stream (See https://docs.mongodb.com/manual/release-notes/4.2/#change-stream).

I disabled read concern majority in my deployment and the change stream works as expected.
If I shut down the secondary node the change stream can't be read out of again.

Is this the expected behaviour? I would have assumed that with 4.2 a majority is not necessary anymore to read the change stream.
If that is the case, is there any way to read the change stream in a PSA deployment with only a minority of data bearing nodes?

This is not the expected behavior. Since you have the majority members of the replica set online, it should work

Just wanted to confirm:

Are you using version 4.2?
After installing/upgrading the replica set, have you set the feature compatibility version to 4.2 by executing the below command?

db.adminCommand( { setFeatureCompatibilityVersion: 4.2 } )

Thank you!


Unable to run mariadb in safe mode When I run following command

mysqld_safe --skip-grant-tables &

I get message

myuser@myvm:~$ 200523 08:24:41 mysqld_safe Logging to syslog.
200523 08:24:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

After that when I type mysql then I get following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

How do I restart mariadb in safe mode to reset root password?

You startt the server with following restrictions

  (Then) ithout a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it also disables remote connections by enabling skip_networking.

This comes from how to reset root passwored

The safe mode is only practical, when doing ridumietery things like resetting roor password.

So explain better what you want to do, maybe there is a better or otheer  solution


connect SQL server file (mdf) with R tool It's possible to connect SQL server file (mdf) with R tool in visual studio 2017? I tried that many times but the connection failed.

From visual studio2017, I want to connect R with Sql server database.
I created R project then from R (tools--> Data --> Add database connection --> Attach database file) but connection failed.

I am assuming you are trying to use SQL Expess localDB. Since you did not give specific error messages my answer is also very generic.

Here is a good reading about LocadDB.  Introducing LocalDB, an improved SQL Express.

Check if you have LocalDB installed and what version by running this in a command window.

sqllocaldb.exe info MSSQLLocalDB

From books oniline:

  Once installed, LocalDB is an instance of SQL Server Express that can
  create and open SQL Server databases. The system database files for
  the database are stored in the users' local AppData path which is
  normally hidden. For example
  C:\Users\\AppData\Local\Microsoft\Microsoft SQL Server Local
  DB\Instances\LocalDBApp1. User database files are stored where the
  user designates, typically somewhere in the C:\Users\\Documents\
  folder.

If it not installed you can install 2016 using this method.

Once you are confirmed that you have localDB installed and version is correct use this document Working with SQL Server LocalDB to create new database(files) or attach existing files. See section SQL Server Express LocalDB.


Count data using multiple date ranges Probably this has been asked before, but I can't figure it out.
I have a phone_clicks table (sql fiddle http://sqlfiddle.com/#!15/855e0/1)

CREATE TABLE phone_clicks (
    id integer NOT NULL,
    date date NOT NULL,
    industry_id integer NOT NULL,
    clicks integer DEFAULT 0 NOT NULL
);

insert into phone_clicks(id, date, industry_id, clicks)
values
(1, '2015-03-16', 1, 15),
(2, '2015-03-16', 2, 7),
(3, '2015-03-16', 3, 0),
(4, '2015-03-17', 1, 12),
(5, '2015-03-17', 3, 4),
(6, '2015-03-17', 4, 22),
(7, '2015-03-18', 1, 19),
(8, '2015-03-18', 2, 35);

This table holds phone click event counts for multiple industry_ids and dates.

Is it possible to count these clicks for all available industry_ids with multiple date ranges as conditions?
I would like to have this output:

------------------------------------------------
industry_id |  today | yesterday | last 3 days |
------------------------------------------------
1           |  19    | 12        | 46          |
------------------------------------------------
2           |  35    | 0         | 42          |
------------------------------------------------
3           |  0     | 4         | 4           |
------------------------------------------------
4           |  0     | 22        | 22          |
------------------------------------------------

I've tried using counting with partition by date, but got nowhere.
Is it possible to select this data in one query?
Additional benefit would be to be able to specify previous months as date ranges: today, yesterday, March, February, January, etc

UPDATE: I've updated the fiddle to define current month, previous months and pre-previous month as the date ranges.
SQL Fiddle: http://sqlfiddle.com/#!15/855e0/46

I'm using PostgreSQL 9.3, but 9.4 solutions are welcome, because we'll be migrating to it soon.

select  industry_id
,       sum(case when current_date <= date then clicks end) as today
,       sum(case when current_date-1 <= date and
                      date < current_date then clicks end) as yesterday
,       sum(case when current_date-4 <= date and
                      date < current_date-1 then clicks end) as last3days
from    phone_clicks
group by
        industry_id

See it in your SQLFiddle.


Is the filestream in SQL Server backed up when using standard backup techniqe I'm using a SQL-Server 2014 database with filestream enabled. To backup this database I'm using the powershell command Backup-SqlDatabase, which is equivalent (as fas as I know) to T-SQL BACKUP DATABASE. The backup is stored in a "mydatabase.bak" file. My question is very simple: is the filestream backed up as well by this command or do I have to take further steps zu back it up?

Backups include both filestream data and relational data. Integrated management is one of the benefits of using filestream/filetable instead of storing files independently.

As suggested by the comments, practice recovery to ensure you can meet your RPO/RTO SLA objectives.


SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has LOG_BACKUP for the log_reuse_wait_desc?

SQL Server 2012 SP1. Database created just a few weeks ago. No replication, no mirroring, no log shipping, and never has had any of those.

We did backup the database and restore to another instance, it shows SIMPLE and NOTHING in log_reuse_wait on the other instance. But I don't think restore to another instance is a good way to reproduce the problem as restore operation rolls-forward/rolls back transactions.

Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause:

MODEL database set to the SIMPLE recovery model.

If the model database has been set to the SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is in the FULL recovery model. Hence waiting for LOG_BACKUP to truncate the log.

I have listed the steps in this link to show how I reproduced the problem.

I think the root cause is if a user database was created (not alter after) with simple recovery model, then it has this problem. I blame it on model database as that is the only way to create a user database with the defined recovery model.

This documented in Microsoft Knowledge Base article 2830400 and is fixed in SQL Server 2012 SP1 CU4 and RTM CU7:

FIX: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"


Is there a way to limit the memory buffer pool for a database? We have several databases on an instance of MS SQL Server 2012. Using Glenn Berry's DMV queries, I see the memory buffer pool used by each database.

Is there a way I can limit AltDB to only use a maximum of 10GB. My goal is that more memory is used by MainDB.

DB     | Memory Buffer Pool in GB
MainDB | 34.00
AltDB  | 24.00

Is there a way I can limit AltDB to only use a maximum of 10GB.

No, not even with Resource Governor.

From BOL

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query.

A good article to read : The clock hands of the buffer cache

  My goal is that more memory is used by MainDB.

Another alternative is to have MainDB on a different instance capped by Max Memory. This way you can reduce the max memory for AltDB.


How to List SYNONYMS that no longer point to a valid object? I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist.

I want to create a query to list them. But I am stuck.

My table OBJECTS_TO_UPDATE has these columns:

OBJECT_NAME
OBJECT_TYPE
OWNER

In words what I want to do goes along these lines:

I filter my OBJECTS_TO_UPDATE table by OBJECT_TYPE = 'SYNONYM'.
Using the Owner and Name I can get the SYNONYM in ALL_SYNONYMS.
Using the TABLE_OWNER and TABLE_NAME and search for it in the dba_objects, if the object doesn't exist, then the synonym is no longer valid.

I want to write a query using all those conditions but I am having issues.

select *
from OBJECTS_TO_UPDATE o2u
where o2u.OBJECT_TYPE = 'SYNONYM' and NOT EXISTS (
                            --select * from dba_objects dobj
                            --where dobj.OWNER
                            select * from ALL_SYNONYMS syn
                            where  syn.OWNER = o2u.OWNER and syn.SYNONYM_NAME = o2u.OBJECT_NAME)

I am stuck/confused about what to do inside the NOT EXISTS.
How can I achieve what I need? I have DBA privs.

Also, is this the best way to achieve what I want? Maybe there are some other views I should use that more direct.

You can query the ALL_OBJECTS view for object_type = 'SYNONYM' and status != 'VALID'.
However all_objects status is set based on the status of the object that it is referring to. Synonyms, which aren't automatically invalidated if the underlying table disappears. So the STATUS column of ALL_OBJECTS is not reliable to use.

Try using this query:

SELECT obj.owner, obj.object_type, syn.synonym_name, tab.table_name
    FROM objects_to_update obj
        INNER JOIN all_synonyms syn
            ON obj.object_name = syn.synonym_name
        INNER JOIN all_tables tab
            ON  tab.table_name = syn.table_name
            AND tab.owner = syn.table_owner
    WHERE obj.object_type = 'SYNONYM' ;


Non-CDB database vs Single tenant CDB I am looking at the prospect of upgrading from oracle 11G to 12C, and I am looking at the cdb vs non-cdb models, and I am having a hard time seeing the benefit of using CDB, without multitenant licensing.

For the most part when I look for information regarding the two, I come up with a lot of forums and blogs saying its the way of the future, non-CDB might be desupported in some future version, its the next big thing for oracle, as reasons to make the switch.  Those all sound like marketing driven reasons to move from non-CDB to CDB, but I am not seeing much information about real benefits of going to a new architecture.  Most of the real benefits seem to come when multitenant cdb is an option, but for the time being I have to assume its not in my case.  We have looked at the cost, and I don't see the option being purchased.

My question then is from a DBA perspective: are there any real benefits from moving to a single tenant CDB vs staying with what seems to be a tried and true design of the non-cdb environment?

Edit: man, two weeks and nothing. Does anyone have anything regarding this?

Having a single tenant CDB allows you to plug/unplug on upgrades, you can use the clone function and it allows stricter separation of admin users in the CDB and the PDB users.

You find this arguments in multiple articles and presentations of Oracle, all hint to the fact that you also should get used to the new normal.

https://blogs.oracle.com/multitenant/single-tenant-configuration


Adding User access to DB2 I have installed DB2 10.1 in a Red hat box. During the installation I was asked to create several users (db2inst1, db2fenc1 etc). I have been using db2inst1 to perform all operations with DB2 and it works perfectly fine. The sqllib files are located in

/home/db2inst1/sqllib

The installation files are located in

/opt/ibm/db2/V10.1/

All users have access to both these folders. I now want to perform the DB2 operations with another user, namely "john". Given that db2 uses the underlying user/password of the host machine, I added the groups dasadm1 and ds2iadm1 to the "john" user (which are the ones listed in db2inst1).

uid=500(john) gid=500(john) groups=500(john),101(dasadm1),102(db2iadm1)

I can properly enter the db2 cmd with the user "john"

[john@rhel6 java]$ /home/db2inst1/sqllib/bin/db2

but when I run my java app as user "john" I get the following error:

SQLException: com.ibm.db2.jcc.am.SqlException: [jcc][10389][12245][4.13.127] Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkError: db2jcct2 (Not found in java.library.path):  ERRORCODE=-4472, SQLSTATE=null

this error does not appear if I run the same java app as user db2inst1. The library paths are the exactly the same for both users. Any idea what is going wrong?

My gut feeling is that you did not update user "john's" profile to invoke the DB2 profile setup. If you do not do this, then you won't have "access" to DB2 per say, even though the binaries are there.

So what you want to do is write a script that checks for your DB2 instance "home" profile script
in your case it would be /home/db2inst1/sqllib/db2profile and if it is there, to invoke it.

if [ -f /home/db2inst1/sqllib/db2profile ] ; then
     . /home/db2inst1/sqllib/db2profile
     if [ ! $? = 0 ] ; then
          echo "Error setting up DB2 instance access"
     else
          echo "DB2 instance access set up"
     fi
fi


Postgres automatic failover, multiple clients to multiple masters I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website.

The majority of implementations that I have found require a single point of failure with PgPool II or PGBouncer proxying the connection for every app server to the correct database.

Example:

App Server 1 \              Spare
App Server 2 -  PGPool II - Master
App Server 3 /              Spare

I'm looking to try a method of automatic fail-over, where each app server will fail-over automatically to a new master DB quickly and correctly. It can still use PGPool or PGBouncer, but each app server should be separate and autonomous with no message/state passing from app server to app server.

Example:

App Server 1 -  PGPool II \ Master 1
App Server 2 -  PGPool II - Master 2
App Server 3 -  PGPool II / Master 3

A maximum time for switch over should be 1-2 seconds.

Does this make sense and is it possible?
Is this an easy to implement via some tool/configuration? If so, how? If not, why?

I can recommend using Slony for PostgreSQL replication from my experience with it.

I can't respond better then the Slony docs section on failover so I'll quote it:

  If some more serious problem occurs on the "origin" server, it may be necessary to SLONIK FAILOVER to a backup server. This is a highly undesirable circumstance, as transactions "committed" on the origin, but not applied to the subscribers, will be lost. You may have reported these transactions as "successful" to outside users. As a result, failover should be considered a last resort. If the "injured" origin server can be brought up to the point where it can limp along long enough to do a controlled switchover, that is greatly preferable.

  Slony-I does not provide any automatic detection for failed systems. Abandoning committed transactions is a business decision that cannot be made by a database system. If someone wants to put the commands below into a script executed automatically from the network monitoring system, well ... it's your data, and it's your failover policy.

To your question: does it make sense?, personally, I would say, no. I agree with the Slony docs that it's better to have a human understand why there was a failure and then manually (and quickly) failover.

Having managed PostgreSQL for more than a decade, unless there's a hardware failure, it's difficult to make it seriously fail. And I recommend involving a human to confirm hardware failure and check the state of the system before failing over.

If you are getting started with a high-availability PostgreSQL, I would first look at the hardware, tuning and RAID configuration behind your PostgreSQL database before considering automatic failover.


Date Format in Sql Server I am importing data from another sources, which has multiple columns but I am facing problem in date column like I have multiple format in this column like:

dd/mm/yyyy
mm/dd/yyyy
mmm-yyyy

Can you guide me how can accommodate these format during migrate data (using cursor to transfer data from temporary table to destination table).

How can i convert mmm-yyyy date format to dd/mm/yyyy format

There is no way to distinguish the meaning of 01/02/2011 and 02/01/2011 if you have this values mixed in the same column. It is that simple.


SQL: Comparing multiple dates I got a table with multiple columns that contain DateTime namely gendate1 up to gendate8. In my stored procedure, the user will have to input a date (@date_start). Now I have to compare the inputted date to the 8 dates which will filter all the data. The problem is that I have to get the two closest dates (out of eight dates) to the given date. Sorry I'm just a beginner in sql.

In response to sir Akina

I don't have the actual code yet because it confuses me of what to do first.

Should I make a temporary table, loop the result of the query and filter each of the result (but how to get the 2 closest dates out of eight dates) then insert it to the temporary table?
or should I put all the condition under the where clause?

Example

 gendate1 = '1/6/2019'
,gendate2 = '4/6/2019'
,gendate3 = '2/6/2019'
,gendate4 = '3/6/2019'
,gendate5 = '5/6/2019'
,gendate6 = '7/6/2019'
,gendate7 = '8/6/2019'
,gendate8 = '10/6/2019'

@date_start = '1/30/2019'

How to get the gendate1 and gendate3? (they are the closest to @date_start)

A slightly different and much more efficient approach (single CIX scan instead of SIXTEEN CIX scans). Assuming this table:

CREATE TABLE dbo.tablename
(
  id int PRIMARY KEY,
  gendate1 datetime,  gendate2 datetime,  gendate3 datetime,  gendate4 datetime,
  gendate5 datetime,  gendate6 datetime,  gendate7 datetime,  gendate8 datetime
);

Then we can use this query to unpivot:

DECLARE @date_start datetime = '20190130';

;WITH x AS
(
  SELECT tp.id, tp.d FROM dbo.tablename AS t
  UNPIVOT
  (
    d FOR ds IN
    (
      t.gendate1,t.gendate2,t.gendate3,t.gendate4,
      t.gendate5,t.gendate6,t.gendate7,t.gendate8
    )
  ) AS tp
)
SELECT id,
  next_date = MIN(CASE WHEN d > @date_start THEN d END),
  prev_date = MAX(CASE WHEN d < @date_start THEN d END)
FROM x
GROUP BY id;

If you want a tie when @date_start is a direct hit, pick one to change to >= or <= (just don't do that to both).

Results with a single id given the sample data in the question:

Plan here is much nicer:


differences in the authentication scheme - could it be because of Kerberos? I am using this script to check the connections to my sql server:

select
C.client_net_address,
S.host_name,
 c.session_id,
 c.net_transport,
 c.protocol_type,
 c.auth_scheme,
 s.program_name
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on c.session_id = s.session_id;

I have noticed that our servers in the UK are generally different to the servers in the US.

US:

UK:

I have found a very interesting link:

Kerberos Authentication requires that you have Service Principal Names registered

and I was wondering if that could be the case for the differences in the authentication scheme on the pictures above.

When the auth_scheme is SQL, that means the user is using SQL authentication, which does not, and cannot use Kerboros.

When a user that is using Windows authentication connects, authentication will first try to use Kerberos.  If Kerberos fails for some reason, authentication will fall back to NTLM.  Probably the most common cause of Kerberos failure is that the correct SPNs are not present in Active Directory (see Register a Service Principal Name for Kerberos Connections.


Postgres VACUUM VERBOSE information I am using Postgres with psycopg2 and sometimes when I run VACUUM VERBOSE table_name; it gives me a line giving the oldest xmin and sometimes it does not. Is there a way to make sure that it always provides it?

Here is the output one that where it didn't give me the value:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.02u sec elapsed 0.03 sec.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

Here is one time where it did give it to me:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

You will notice that the second time there is the following line:

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410

This does not appear to be a conditional.  The extra information was added in v10 with commit 9eb344faf5, and is always printed in that version and up for a VACUUM VERBOSE (it apparently is not printed for a CLUSTER, which does seem undesirable).

If you want consistent behavior, you need to upgrade all your database instances to the same version of PostgreSQL.


Design ER with complicated relationships between different tables I try to design a database and I am stuck dealing with a part of it. The problem is very scientific, so for simplicity, I will try to give you a simple representation that accurately matches the case.

Imagine we have an oven with many drawer trays (each can only be used once and then get stored), and each tray is split in a specific number of compartments. That is either:

8 compartments
2 compartments
no compartments (or 1 big if you like)

You can call it 3 types of trays if that's better for the database design, in case some other type becomes available in the future.

Now, we have projects (imagine persons) and each project can have 1 or more iterations (imagine bakes). Each iteration can have 1 or more samples (imagine cakes). Each iteration is assigned to 1 tray. Two or more iterations of different projects that run at the same time, can be assigned at the same tray. However, two iterations of the same project CANNOT be assigned at the same tray. Imagine a new iteration will come at a later date, so since the trays are single-use, the next iteration has to be assigned on a new tray.

Each sample can be stored in 1 or more compartments (we can divide a cake in smaller cakes and bake them in several compartments of the same tray). Also, 1 compartment can hold 1 or more samples from the same iteration of the same project (imagine we divide the cake to 4 parts, and we bake 2 parts in compartment_1 and 2 in compartment_2), or from an iteration of a different project.

So in other words, two or more projects can run in parallel, and their iterations can be assigned to one tray. Future iterations of either of the projects will be assigned in a different tray. Each sample and compartment can be related in any order, and we need to keep track of everything.

I have tried several things, but nothing feels good enough to post here as ER. My tables are the ones I have in bold: project, tray, sample, iteration, (not sure about compartment).

Since a project can use many trays, and a tray can host many projects, I use the iteration table as a join table. But now I have no clue where I should connect the sample.

A tray is hosting many samples, while 1 sample only uses 1 tray, so I could connect them 1-to-many. However, I also need to keep track of whether the sample is stored in 1 compartment or spread out on more. Then I also need to know which project each sample belongs to.
Similarly, if I relate sample to project, I will not have information about the tray relation to the sample.
An iteration may have 1 or more samples, while 1 sample can be in 1 iteration, so I could create a relationship sample-iteration. This way I will be able to know both the project and the tray each sample is related to. However, the compartment problem remains.

Sounds like a chasm trap, so a triangle relationship could be the way to go, but I am not experienced enough and I'm not sure how to design something like this.

To tackle the compartment problem, I could use a field in sample to store the compartment information and use the software to make sure the compartment values are as supposed to be. But not sure this is the right choice.

Any help to design an ER for this part of the project is highly appreciated.

Your idea of having iterations as a join table for trays and projects seems good.

samples are obviously strongly related to iterations, because one sample belongs an iteration, and an iteration contains several samples. Therefore I would make an iterations 1..n samples association.

Then, compartments belongs to a tray. But we can also see compartments as generic identifiers which, when associated with a tray, defines a specific "location" for a sample. We already know to which tray samples of an iteration are associated. Also, a compartment can contain several samples, and a sample can be spread into several compartments. So we need a join table between samples and compartments.
If we keep compartments as generic identifier (see them as slot numbers in a tray), we don't need relationship between trays and compartments. When filling a sample, we already know to which tray it belongs through the iteration. The user will just have to fill the compartment(s) where the sample is located.

You can also enforce the compartment choices for the user by setting a type to the tray, which let you know what compartments can be choosen.