texts: Reduce read write delays in specific database setup

texts:
I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that adds data every second. Each row is uniquely indexed by a serial type.

My requirements are:

The latest available data (~300 s of data) on the table is required to be displayed on a GUI monitor tool.
Also a cleanup program needs to remove data that is older than 3 days from the table and write it to a file.

When I run this setup for over a day, I notice my query delays are increasing with time (upto 30 ms on insert and upto 1.5 s on fetch ). How do I keep my delays constant and below 1 s in both cases ). I am interested in a solution that is scalable

I am currently using PostgreSQL database. The insert and cleanup program are written in C using the ODBC driver and the GUI toolkit is web based with PHP/Apache as the back-end.


texts: What does TOAD do, when I terminate script execution?

texts:
When it stops the execution of a long running script.  The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad.

2nd EDIT:

Just found that this is a feature of Toad and not of Oracle

In SQL developer, you can stop execution of long running queries and similar to SQL Server Management Studio you get can modify your query immediately.


texts: What all to start for oracle database server

texts:
I am a Db guy familiar with Postgresql and Mysql. I have just started with Oracle and I have installed oracle 11g R2 on a RHEL-6 server. The installation was successful (the runInstaller script). During the install I had opted for Create and configure a database and I gave the DB the name "orcl".

After everything finished successfully, when I issued "ps aux | grep -i 'oracle'" in my shell (bash), I had got a log of processes (dont' remember the names of all of them). However, I stopped the server and now I want to know how I can start the oracle service. I have seen many commands to start Oracle(this link, so I am confused what are the necessary things that I must start. If it was Postgres or MySQL, it would have been only /etc/init.d/(mysqld|postgres) start.

So what are the things that I should start ? and please give me a brief a description of what that command does.


texts: How to reset failures count to zero when using dbms_jobs?

texts: Is there some way to reset the failure count column to 0 in dba_jobs view? Sometimes a job fail and after fix it I need to wait the next scheduled execution, during this time I'd like to reset to 0 to remove the alert from the monitoring tool.


texts:
Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, then years, then days of the month, then cross join'd everything together to create a valid list. I'd rather do that then create a giant table listing all valid dates from the start to the end of time. Converting it to a set of views, however, doesn't seem to be working:

create view `Numbers` as
    select 0 N union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all select 8 union all select 9;
create view `Months` as
    select n.N+1 MonthNum union all select 11 union all select 12
    from `Numbers` n;

Error from MySql: Error Code: 1109. Unknown table 'n' in field list.


texts: How we can convert postgres array :- ARRAY['{"id": "1"}', '{"id": "2"}'] to JSONB '[{"id": "1"}, {"id": "2"}]'


texts: Return all except some fields using findOne() in mongodb for node.js

texts:
I am trying to use the following script to query for a document but the returned document still contains the field (password) that I don't want.

const client = await MongoClient.connect(
                      MONGODB_CONNECTION_STRING,
                      { useUnifiedTopology: true }
                   );
const db = client.db(DB_NAME);
const collection = db.collection(collectionName);
const data = await collection
                .find(
                    {
                        _id: someId
                    },
                    {
                        password: 0
                    }
                );

It supposed to work as I have read in the mongodb documentation but I didn't find any documentation on findOne in mongodb for node.js.


texts: Logging in to phppgadmin remotely Ubuntu 14.04 Amazon EC2

texts:
I created an Ubuntu server instance on Amazon EC2 and installed php, postgreSQL, phppgadmin, and any other required features.

After SSH'ing into the server using command prompt, I executed the following operations:

'sudo apt-get update'
'sudo apt-get install apache2 libapache2-mod-php5 php5'
'sudo apt-get install postgresql postgresql-contrib'
'sudo apt-get update'
'sudo apt-get install phppgadmin'

'sudo nano /etc/apache2/conf.d/phppgadmin'
- uncomment line 'allow from all', save and exit

'sudo nano /etc/apache2/sites-enabled/000-default'
add the following text:

<Directory "/usr/share/phppgadmin">
        AuthUserFile /etc/phppgadmin/.htpasswd
        AuthName "Restricted Area"
        AuthType Basic
        require valid-user
</Directory>

- save and exit

'sudo apt-get install apache2-utils'

'sudo service apache2 restart'

'sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf'
'sudo service apache2 restart

The result:
http://ec2-54-174-161-124.compute-1.amazonaws.com/phppgadmin/

My issue now is that I don't know what to do so that I can access phppgadmin and start creating a database.  A tutorial explained how to setup a password to access the page that was linked to above, but this did not work because as you can see, the page is accessible without a password.

My questions:
1) It is an issue (in terms of security) that there is no password to access the page that I linked to? If so, how can I fix this?
2) How can I gain access to phppgadmin after I click on the 'PostgreSQL' link in the sidebar that shows a container with a red X on it? I have searched and searched and I can't figure out how to setup a user or access it as an administrator/root/postgres user


texts: How many database licenses do we need for setting 2 servers (nodes) into a cluster?

texts: Suppose we have a server that has only 1 CPU with 1 core, so we need 1 Oracle database license for installing a database into it. Now, if we would need to add a second server, combine them into cluster and install database into it, then how many licenses do we need - 2 database licenses and 1 Clusterware licenses or 1 database license and 1 cluster license? I'm not sure, how Oracle treats joined into Oracle cluster servers - as one server or still as separate servers? Because we would need to buy expensive Oracle cluster software, then it would be logical for Oracle to treat joined servers as one.


texts:
I work in a fintech company and we are redesigning our data system.

We have around 120 GB of compressed data stored in the filesystem.
The data concerns around 80K different timeseries each one of them with around 8K up to 5M rows.
The total estimated number of rows is 8 billion. Each row should hold 6 numeric values, time and an identifier.

The queries we want to run are:

Give me all data for a timeseries between two timestamps
Give me all data for a timeseries in single element buckets of n minutes

Everyday we update around 10K of these timeseries with 8K up to 40K records that concern the data of the last month. That is around 8M rows and takes around 4 hours.

We are looking for something that can handle the daily write load in less than 3 hours while maintaining fast query performance.
Our queries concern 3K timeseries, they scan around 3M rows and return 3.5 K rows for each one of them. This currently happens in 1 minute.

The current system is not so sophisticated, all data is kept in a file server and is synchronized daily to the local computers of the interested parties.
The system worked well for some time and now we are looking for something more robust and scalable.

Our hard requirement is the daily ingestion time but we have some flexibility on how much the reading queries should take.

Can a database give us performance and scalability?
If so what would you choose and why?
What kind of infrastructure do we need to support such a system?


texts:
I posted this first in the aws forums before i learned that there's a stackoverflow for DBs but i would ask this here as well.

We recently took over management of a wordpress site using a MySQL DB instance. a few days ago, it ran out of space which we found unusual because the site itself isn't a super busy site and it doesn't really have a lot of stuff in it when checked through wp-admin. To get the site to load, we increased the allocated storage from 185GB to 250GB. We then deleted the transients then optimized the tables and we got almost all the 250GB back. We then observed the monitoring windows in aws and we noticed that the free space was going down even if the site isn't that active at that time (loses around 2.5 GB every 24 hours). There are no running cron jobs since cron is disabled and checking show processlist isn't showing any running queries which we found strange.

We then realized that the write iops was pretty high at around 150-200 as reported through aws rds monitoring and is not going down considering that there's very little activity on the site. This was probably what caused the space to fill up before.

Going through some of the suggested links for this issue here in dba.stackexchange, i found this link which suggested to run the command SHOW ENGINE INNODB STATUS and i found this

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3876, Main thread ID=47504970233600 , state=sleeping
Number of rows inserted 1695335, updated 24452885, deleted 684983, read 11973811927
0.00 inserts/s, 2.39 updates/s, 0.00 deletes/s, 586.80 reads/s

Which doesn't seem to be a great number of updates. We're not sure where to check next exactly. If it would help, the instance is a t3.large instance

Thanks


texts: How to check with SQL whether a column is an identity column for an Access database?

texts:
Knowing how to check whether a column is an identity column for Microsoft SQL Server, I currently still found no solution for doing the same with SQL for a Microsoft Access database.

My question:

How can I check with SQL whether a column is an identity column for a Microsoft Access database?

(Since I want to do it in my code, an ADO.NET solution would be helpful, too, although it seems, that this is not possible)


texts: How to get SQL Server Compact 4.0 Database content query?

texts:
I need to migrate data to a SQL Server 2014 database from an existing SQL Server CE 4.0 database. I'm having difficulty because my computer runs SQL Server 2012 so I'm not able to port the data via a graphical wizard.

I am able to run scripts on all databases. I just don't know how to perform a the data migration without a graphical interface.


texts:
We ran across an error today:

  Error: 3967, Severity: 17

and from what I can tell it's basically 'full version store'. This has raised some questions that I've had trouble answering, as at my shop we've never had this error before:

If there is a preset max size (based on total tempdb size or otherwise), is it dynamic?
Is there a setting that would make it dynamic if it isn't?
Can we set up alerts based on this? (i.e. DMVs to use to track size etc.)


texts: How do I place leading zeros for numbers less than 10 without affecting those 10+?

texts:
Probably a newb question, but...

I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original number if it is 10 or greater (i.e. 1=01, 10=10 and 300=300).

SELECT DISTINCT RIGHT('0'+CONVERT(VARCHAR,[FacilityCode]),3) FROM...

This returns 1=01, 10=010 and 300=300 (using the same examples as above)

EDIT: I'm trying to do this within a query so that I can do lookups without altering either table.

Can someone lend a brother a hand here? My brain just ain't working.


texts: Best way to async data inserts into timescaledb

texts:
Having 3 files with one data set of daily data inside:

2019-09-11.txt
2019-09-12.txt
2019-09-13.txt

Having lines like:

2019-09-11 15:59:37.459802,1
2019-09-11 15:59:38.959802,1
[...]

The files are sorted by a timestamp, which is the main index in the hypertable:

CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data INT

So it is a lot of data...
Is there a way to reach a higher insert performance rate by asyncing the INSERT INTO commands to one insert process per input file/date?


texts: Structuring data in MongoDB for a to-do list type application

texts:
This is a fairly high level question as I am in the initial stages of planning this application, and am also very new to MongoDB and document-based databases in general. Disclaimer aside, I am building a to-do list type application whose two main types of data are users and items (aka tasks or todo's), and my question is, what would be a good way to structure this data in mongodb, if I want it optimized more for read speeds rather than updates?

My initial thought, based on my limited knowledge of mongodb, is that since it allows embedded type data structures I could basically store it in a single collection like this:

{
    username: "joe",
    password: "...",
    items: [
        {
            text: "This is a task",
            completed: 1
        },
        ...
    ]
},
...

However, will this make it more difficult to update/remove existing items since they are not themselves stored as separate documents, and therefore lose things like auto-generated unique object ID's, ect? Also each user could potentially have a relatively large number of items associated with them (possibly 1000's). Is embedding large lists in parent documents like that a bad idea?


texts:
I was recently trying to create some large strings containing generic test data for a question here.  It seems that I used to know of a way to multiply a string.  However, I can no longer remember the syntax.

I'm looking for something like:

SELECT 'A' + ('a' * 1000) + 'ha!'

To come up with "Aaaaaaaaaaaaaaaha!"  (Well, much longer, of course.)

Is this possible in T-SQL? (Or am I thinking of some other language?)  Also, are there any other techniques to generate large strings?


texts: Outage due to Replica Set Protocol Version upgrade?

texts:
My production system has a replica set created in MongoDB v3.0, which still has protocol version undefined (i.e. version 0). It is now running v3.4 and I intend to upgrade to replica set protocol version 1, but I'm wary of what will happen while the replica set reconfigures itself.

Is there any risk of an outage at all? Either if all nodes are running normally, or if any nodes have problems during the change? The documentation warns that reconfiguration can trigger the current primary to step down in some situations but doesn't say which situations.

Apart from the protocol version, I am making no other configuration changes; for example, the member priorities will be unchanged so the same priority node will hopefully remain primary.

The current config is:

rsMyApp:PRIMARY> rs.conf()
{
  "_id" : "rsMyApp",
  "version" : 26139,
  "members" : [
    {
      "_id" : 4,
      "host" : "DB2:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 1,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    },
    {
      "_id" : 5,
      "host" : "DB3:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 1,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    },
    {
      "_id" : 1,
      "host" : "DB1:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 2,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    }
  ],
  "settings" : {
    "chainingAllowed" : true,
    "heartbeatIntervalMillis" : 2000,
    "heartbeatTimeoutSecs" : 10,
    "electionTimeoutMillis" : 10000,
    "catchUpTimeoutMillis" : 60000,
    "getLastErrorModes" : {},
    "getLastErrorDefaults" : {
      "w" : "majority",
      "wtimeout" : 0
    }
  }
}


texts: How to install MySQL 5.1 on Ubuntu 10.10?

texts:
I researched a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps to be followed to install Mysql 5.1 ?

It gives me an error and stop the execution even when i use -f.
This is the message from command prompt.

The following packages have unmet dependencies:
 mysql-server-5.1 : Depends: mysql-client-5.1 (>= 5.1.61-0ubuntu0.10.10.1) but it is not going to be installed


texts: Optimize query for orders with unseen comments

texts:
I have following MySQL tables in an application I have to support:

CREATE TABLE orders
(
  id         int     auto_increment primary key,
  author     int(8)  not null,
  manager_id int(10) default 0 not null
);

CREATE TABLE order_items
(
  id            int(12)         auto_increment primary key,
  order_id      int             not null,
  amount        int(3)          not null,
  price         decimal(10, 2)  null
);

CREATE TABLE comments
(
  id            int(10)      auto_increment primary key,
  module        int(10)      not null,
  item_id       int(10)      not null,
  item_type     varchar(8)   not null,
  message       text         not null,
  author        int(10)      not null
);

CREATE TABLE comments_seen
(
  user_id    int(10) not null,
  comment_id int(10) not null,
);

Comments table is shared between chat, testimonials, order-level comments and order-item-level. Total order comments comprise of order-level and order-item-level comments, which can be JOIN'ed with comments.module = 5 AND comments.item_id = orders.id and comments.module = 4 AND comments.item_id = order_items.id respectively. If a comment was read by user (e.g. manager) then there's respective row in comments_seen, referencing comment id and user id.

What would be the most performant query to list orders with unread comments?

I need to show a list of orders accessible to current user (e.g. order author or order manager), but only show orders with unread comments.

UPD1: My current attempt is as following:

(
  select o.id
  from cart_orders o
  inner join comments c on
    (c.item_id = o.id and c.module = 5)
  left join comments_seen cs on cs.comment_id = c.id  and cs.user_id = 2
  group by o.id
  having sum(cs.comment_id is null and c.is_deleted is false) > 0
  order by o.add_date
)
union distinct
(
  select o.id
  from cart_orders o
    left join order_items oi on o.id = oi.order_id
    inner join comments c on
      (c.item_id = oi.cat_id and c.module = 4)
    left join comments_seen cs on cs.comment_id = c.id and cs.user_id = 2
  group by o.id
  having sum(cs.comment_id is null and c.is_deleted is false) > 0
  order by o.add_date
)

But that gives me a list of unique order ids, which either have unread order comments, or unread order item comments. Is there way to represent it in following manner:

+----------+----------------+--------------------+
| order_id | order_comments | orderitem_comments |
+----------+----------------+--------------------+
|        1 |              0 |                 10 |
|        2 |              4 |                  1 |
|        3 |             13 |                 25 |
|        4 |              2 |                  0 |
+----------+----------------+--------------------+

The NOT IN seem not to perform well and I know there's a way to make it work with proper JOINs, but I just can't wrap my head around it.

MySQL server version is 5.5.48.


texts: Retain indent formatting when compiling a stored procedure without Data Studio

texts:
When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out.  Only when I compile in Data Studio is the formatting retained, but I would like to deploy projects with a script.
I expect there is something simple I am missing, but have not been able to find a solution or even any other posts with the same issue.
Using CLPplus I ran this script file @c:\temp\proc1.spsql
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end

The SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'; produced the the following:
create or replace procedure proc1()
begin
declare vId int default 0;
call dbms_output.put_line(vId+1);

/*
Testing
*/
end


texts: SQL Server - Why am I getting TCP connections without sessions?

texts:
I enabled SSL with Forced Encryption enabled, using a self-signed certificate.
Then I've set up automated monitoring to detect not secured connections (seems redundant, I know, but bear with me here), using the following T-SQL query:
SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')
, ' ', QUOTENAME(ISNULL(ses.host_name, 'unknown host'), '(')
, ', ', ISNULL(QUOTENAME(ses.program_name), 'unknown program')
, ', to ', ISNULL(QUOTENAME(DB_NAME(ses.database_id)), 'an unknown database')
, ', Session(s): ', COUNT(ses.session_id)
), COUNT(con.connection_id) AS NumberOfConnections
FROM sys.dm_exec_connections AS con
LEFT JOIN sys.dm_exec_sessions AS ses
ON ses.session_id IN (con.session_id, con.most_recent_session_id)
WHERE encrypt_option = 'FALSE'
AND net_transport = 'TCP'
AND client_net_address NOT LIKE '<%'
GROUP BY COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)
, client_net_address, ses.host_name, ses.program_name, ses.database_id

Later, I started getting alerts resulting from this query, with output that looks like this:
Not secured connection(s) detected of an unknown login from [x.x.x.x] (unknown host), unknown program, to an unknown database: 0
(the x.x.x.x replaces actual IP addresses that came from one of our customer's networks)
This means that we're detecting TCP connections (in sys.dm_exec_connections) without corresponding sessions (in sys.dm_exec_sessions).
Based on what I found in Microsoft documentation, these scenarios could happen as part of Availability Group, Database Mirroring, or Service Broker architectures... But we're not using any of those in our instance!
This should not be happening... Does anyone have any idea how to explain this? Could this happen due to some kind of port scanner perhaps?
Thanks!
Version details:
Microsoft SQL Server 2019 [Enterprise] (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
    Mar 14 2020 16:10:35
    Copyright (C) 2019 Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)


texts: Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report

texts:
I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It is only printing the output of first commands. Can someone please help me with joining the result of all three variables? What I'm trying to do is writing a script to automate generating our SQL Server Inventory and Capacity assessment report.

Here's my code so far:

$Servers = Get-Content 'C:\Users\temp\ServerList.txt'

$reportData = @()

foreach ($Server in $Servers) {

  if (Test-Connection -ComputerName $Server -Count 1 -ErrorAction 'SilentlyContinue') {

    $OSInfo=Get-DbaOperatingSystem -ComputerName MyServer| Select ComputerName, OSVersion, Version, Architecture
    $DiskSPace=Get-DbaDiskSpace -ComputerName MyServer| Select ComputerName, Label, Name, Capacity, Free
    $SQLInstanceInfo=Connect-DbaInstance -SqlInstance MyServer | Select DbaInstanceName, Edition, NetPort, IsClustered, Processors, ProductLevel, ServiceName,

    $reportData +=$OSInfo
    $reportData +=$DiskSPace
    $reportData +=$SQLInstanceInfo

  }

  else {

    write-host $Server "not connected"
  }
}

Write-Output $reportData | Format-Table


texts: Modify linked server definition (Oracle) in SQL Server 2012

texts:
I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works with clustered servers). This works fine.

Now the Oracle database has been moved to another cluster, so I edited TNSNAMES.ORA. Oracle SQL Developer works fine.

It seems that SQL Server doesn't re-read TNSNAMES.ORA but stores the connection details internally, because even after a server reboot all queries time out.

Does anyone know of a solution other than deleting and recreating the linked server ?

(That's what I did to resolve my issue, but it shouldn't be that hard !)


texts: What does the "Edit top 200 Rows" SSMS option actually do?

texts:
I am new the SQL Server DBA role. I have always been the developer, but due to being the only one at my company with moderate SQL Experience, the role has fallen onto my plate. And today I came face to face with a question I did not have an answer for.

I am familiar with the option in SSMS to "Edit top 200 Rows" for a table, but I never understood the HOW of it. I ask this question because a Contractor who's been working on our system since before I took over was complaining of slow query results. The query he had written pulls in data over a large time period with a final result set in the ballpark of 800,000 rows. A sizable chunk of data.

What surprised me more was that said query took over 9 minutes to run, which did not seem correct to me. I asked him to send me the query. I took his query and ran it and it returned for me in 28 seconds. A sizeable difference compared to the over 9 minutes it took him. We both ran the query directly on the server so I knew that was not the case. I then asked him to show me how he ran it. He then opened up the table with the "edit top 200 rows" option, backspaced the TOP 200, and added his conditionals after the FROM and proceeded to execute it. I told him that was his issue there. He told me it should not be. Now I know that this is a "no no" for large data sets, but I lacked the knowledge to explain why.

I was hoping for a little help from the community here. What does the "Edit Top # Rows" option actually do?


texts: Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS

texts:
I am running Oracle 11GR2, Enterprise Edition Release 11.2.0.3.0.  I got one of my Test DBs corrupted today.  Luckily, I have 2 weeks of backups in the Net Backup server, so I thought, "This is easy, I just restore from the latest backup and I should be good in about 2 hours!"

But I kept getting this Oracle ORA-01190 error.  So I changed my restore time about 30 minutes earlier, than I got the same error.

  SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

After I made the restore time changes, I got this error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

This is my rman restore scrpt

run
 {
 allocate channel D1 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D2 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D3 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D4 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 set until time "to_date('16-MAR-2016 15:20:00','DD-MON-YYYY HH24:MI:SS')";
 restore controlfile;
 restore database;
 alter database mount;
 recover database;
 alter database open resetlogs;
 }

Shall I start to use SCN method to restore my database?  Is there a clear indicator for me to see the time I can surely restore my database from its backup files?

Thank you so much!


texts: What is the difference between select from function and select function

texts:
I am doing a test with generate_series function on Postgres v.9.6 on Ubuntu 16.04. So, I get a curiosity about loops (loops=100000) when calling that function as below.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT a FROM generate_series(1,10));
Aggregate  (cost=627262.00..627262.01 rows=1 width=8) (actual time=60.534..60.534 rows=1 loops=1)
  ->  Seq Scan on t5  (cost=0.00..627137.00 rows=50000 width=0) (actual time=0.048..50.267 rows=100000 loops=1)
        Filter: (SubPlan 1)
        SubPlan 1
          ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.000..0.000 rows=1 loops=100000)
Planning time: 0.161 ms
Execution time: 60.599 ms

However, If I change the query. The loop and result will change.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series(1,10) a);
Aggregate  (cost=1488.51..1488.52 rows=1 width=8) (actual time=0.050..0.050 rows=1 loops=1)
  ->  Nested Loop  (cost=17.80..1363.51 rows=50000 width=0) (actual time=0.025..0.046 rows=10 loops=1)
        ->  HashAggregate  (cost=17.51..19.51 rows=200 width=4) (actual time=0.012..0.014 rows=10 loops=1)
              Group Key: generate_series(1, 10)
              ->  Result  (cost=0.00..5.01 rows=1000 width=4) (actual time=0.004..0.007 rows=10 loops=1)
        ->  Index Only Scan using t5_pkey on t5  (cost=0.29..6.71 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=10)
              Index Cond: (id = (generate_series(1, 10)))
              Heap Fetches: 10
Planning time: 0.124 ms
Execution time: 0.086 ms

Here's script to create table

CREATE TABLE t5 ( id serial primary key, a int, b text);
INSERT INTO t5 (a,b) SELECT a, 'test_' || a FROM generate_series(1, 100000) a;


texts:
I've restored database to another host, backup of production db was restored to our testing environment to provide actual data. And it looked like everything is fine, users switched to new db and started to work with it. But then, after several hours of work, we found db is shut down, with error 822 in alertlog. I started it manually and after 4 hours of work it crashed again with same error.
Haven't found any information abour error 822, hope you'll help me to solve this problem, what can cause such crashes?
Messages of log.xml related to last error:

<msg time='2018-07-26T14:26:51.373+03:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='oracle-uat.dwh.tech' host_addr='10.233.31.34' module=''
 pid='11440'>
 <txt>System state dump requested by (instance=1, osid=11429 (PMON)), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2018-07-26T14:26:51.511+03:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='oracle-uat.dwh.tech' host_addr='10.233.31.34' module=''
 pid='11440'>
 <txt>System State dumped to trace file /u02/app/oracle/diag/rdbms/dbuat/DBUAT/trace/DBUAT_diag_11440_20180726142651.trc
 </txt>
</msg>
<msg time='2018-07-26T14:26:51.536+03:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='oracle-uat.dwh.tech' host_addr='10.233.31.34' module=''
 pid='11429'>
 <txt>PMON (ospid: 11429): terminating the instance due to error 822
 </txt>
</msg>
<msg time='2018-07-26T14:26:53.171+03:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='oracle-uat.dwh.tech' host_addr='10.233.31.34' module=''
 pid='11440'>
 <txt>Dumping diagnostic data in directory=[cdmp_20180726142651], requested by (instance=1, osid=11429 (PMON)), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2018-07-26T14:26:54.422+03:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='oracle-uat.dwh.tech' host_addr='10.233.31.34' module=''
 pid='11429'>
 <txt>Instance terminated by PMON, pid = 11429
 </txt>
</msg>

It's Oracle 11.2.0.4 running on Oracle Linux


texts: Split cases in WHERE clause based on boolean variable

texts:
I have to split a WHERE clause based on a boolean variable:

If ShowZeroVariable is true, I only what to show rows value 0 in a certain integer column.
If false I want to show all that are greater than 0.

Something like this:

select IntField from Table
where
IntField
case
when ShowZeroVariable  = 'true' then = '0'
else <= '0'
end

I can only get it to kind of work by casting to text
(But this doesn't filter out the zero values when false):

select IntField from Table
where
IntField::text like
case
when ShowZeroVariable  = 'true' then '0'
else '%'
end

Can't seem to get a WHERE clause CASE statement to work with operators < / = / >.


texts: SQLite3 not using covering index with json_extract expression

texts:
I am attempting to create an index in SQLite3 (3.18) using json_extract expressions. My aim is to execute queries that only require the index to yield results. The reason for this is that json_extract is an expensive operation which would hinder performance when operating on larger data sets and/or values. I concluded I need a covering index to suit my needs.

Step 1 - Testing the theory using a normal table structure

CREATE TABLE Player (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FirstName TEXT NOT NULL,
    MiddleName TEXT,
    LastName TEXT NOT NULL
);

CREATE INDEX Player_FirstName ON Player (
    FirstName ASC,
    LastName ASC
);

EXPLAIN QUERY PLAN SELECT
    FirstName, LastName
FROM
    Player
WHERE
    LENGTH(LastName) > 10
ORDER BY
    FirstName
LIMIT
    10
OFFSET
    0

Yields

SCAN TABLE Player USING COVERING INDEX Player_FirstName

This is exactly what I expect. The query planner figured that the Player_FirstName index is appropriate due to the ORDER BY clause, and since the WHERE statement operates only on a value that is also in that index, it doesn't need to read the table. Finally, the SELECT statement includes only the indexed columns therefore resulting in a query that doesn't touch the table at all.

Step 2 - Testing the theory with an extract expression

CREATE TABLE PlayerJ (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Data TEXT NOT NULL
);

CREATE INDEX PlayerJ_FirstName ON PlayerJ (
    JSON_EXTRACT(Data, '$.FirstName') ASC,
    JSON_EXTRACT(Data, '$.LastName') ASC
);

EXPLAIN QUERY PLAN SELECT
    JSON_EXTRACT(Data, '$.FirstName') AS FirstName,
    JSON_EXTRACT(Data, '$.LastName') AS LastName
FROM
    PlayerJ
WHERE
    LENGTH(LastName) > 10
ORDER BY
    FirstName
LIMIT
    10
OFFSET
    0

Yields

SCAN TABLE PlayerJ USING INDEX PlayerJ_FirstName

This is not what I expected. The query planner seems to have figured out that the ORDER BY clause is on JSON_EXTRACT(Data, '$.FirstName'), and thus seems to have selected the appropriate index. But that is where my reasoning ends abruptly. What is going on here? I would have expected the query planner to figure out that this is the same as the previous test, and the index would be used as a covering index. But it doesn't.

Why not? And how can this second test be modified so that it runs only against the index?


texts: MySQL - Information_schema Events - How persistent or retention is managed

texts:
I'm a MS SQL guy and understand agent job history.
I'm now working with MySQL.
I've searched hi and low but I can't find any solid info on how event data within information_schema.events is persisted, retained for.
I plan to setup some MySQL Events but need to understand how reliable the logs are.
I don't think we have anything in our my.cnf with regards to retention periods of information_schema.
If it's forever - should I try and clean this up with some house-keeping job?
I am making events to fire once a week. Ideally these would be retained for 180 days.
Events that fire more regularly every 5 minutes, perhaps should only be retained for a month.
how do I extend this/ manage this - do people log to a separate table that you can manage yourself?


texts: Designing MySQL Database For Two Different User Types (Staff and Client)?

texts:
I'm creating a web-app that will connect to a MySQL database.  This app will have 2 users:  a Staff and Client that can both log in from the same login page.

Both Staff and Client share the following common attributes:

staffID/clientID
status
timestamp update
username
password
email
image profile
first/middle/last name
gender
date of birth
address
phone

But the Staff has the unique attributes:

position
license
badge
date hired

And the Client has the unique attribute:

company name

1.) Would it be advisable to put all the common attributes in one single Users table with a column user_type to determine whether it's a Staff or a Client that logs in or make two separate tables for Staff and Client that have the exact same attributes?

2.) Would it be advisable to further separate the following attributes:

gender
date of birth
address
phone

into another table since they will not be queried as much (these attributes are only queried when viewing users' profiles)?  Will this help speed up query time for, say, log ins?

3.) Would it be ideal to separate the image profile attribute into one table Image_Files or keep it within the same table with all the other attributes?

As of now, I'm leaning on making the following tables with columns:

USERS table:

userID [PK] (formerly staffID/clientID)
status
timestamp update
username
password
email

USER_DETAIL table:

user_detailID [PK]
userID [FK]
gender
date of birth
address
phone

STAFF_DETAIL table:

staff_detailID [PK]
userID [FK]
position
license
badge
date hired

CLIENT_DETAIL table:

client_detailID [PK]
userID [FK]
company name

IMAGE_FILES table:

imageID [PK]
userID [FK]
image pathfile pointer

Is this database design unnecessarily complicated?  If so, how may I streamline it to make it more simpler, faster to query, and yet still satisfy the unique attributes of each entities?


texts:
When retrieving a text value, which has been stored in a varbinary field, and converting back to text, is there a way to determine whether it was stored as a VARCHAR or NVARCHAR string?  If it is unknown, telling the CONVERT function it is a NVARCHAR string, when it is in fact VARCHAR will return gibberish.  Run the below for an example:

IF OBJECT_ID(N'dbo.Temp') IS NOT NULL
  DROP TABLE dbo.Temp;

CREATE TABLE Temp(
   ID INT IDENTITY(0,1) NOT NULL
  ,Test VARBINARY(50) NULL
);

INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), 'varchar test string'));
INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), N'nvarchar test string'));

SELECT CONVERT(VARCHAR, Test) AS [VARCHAR], CONVERT(NVARCHAR, Test) AS [NVARCHAR]
FROM dbo.Temp;


texts: Error "cannot connect to server" installing MongoDB on Ubuntu

texts:
I'm trying to install MongoDB on Ubuntu 10.04.

I followed these installation instructions. I got the following error:

Error: couldn't connect to server 127.0.0.1:27017 src/mongo/shell/mongo.js:91

What do I need to do to fix this?

Here is the log output:

**** SERVER RESTARTED *****

> Sun Sep 23 22:36:20
Sun Sep 23 22:36:20 warning: 32-bit servers don't have journaling enabled by default. Please use --journal if you want durability.
Sun Sep 23 22:36:20
Sun Sep 23 22:36:20 [initandlisten] MongoDB starting : pid=1916 port=27017 dbpath=/var/lib/mongodb 32-bit host=bruce-ubuntu1004
Sun Sep 23 22:36:20 [initandlisten]
Sun Sep 23 22:36:20 [initandlisten] ** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data
Sun Sep 23 22:36:20 [initandlisten] **       see (http://)blog.mongodb.org/post/137788967/32-bit-limitations
Sun Sep 23 22:36:20 [initandlisten] **       with --journal, the limit is lower
Sun Sep 23 22:36:20 [initandlisten]
Sun Sep 23 22:36:20 [initandlisten] db version v2.2.0, pdfile version 4.5
Sun Sep 23 22:36:20 [initandlisten] git version: f5e83eae9cfbec7fb7a071321928f00d1b0c5207
Sun Sep 23 22:36:20 [initandlisten] build info: Linux domU-12-31-39-01-70-B4 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:39:36 EST 2008 i686 BOOST_LIB_VERSION=1_49
Sun Sep 23 22:36:20 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/var/lib/mongodb", logappend: "true", logpath: "/var/log/mongodb/mongodb.log" }
Sun Sep 23 22:36:20 [initandlisten] Unable to check for journal files due to: boost::filesystem::basic_directory_iterator constructor: No such file or directory: "/var/lib/mongodb/journal"
**************
Unclean shutdown detected.
Please visit http://dochub.mongodb.org/core/repair for recovery instructions.
*************
Sun Sep 23 22:36:20 [initandlisten] exception in initAndListen: 12596 old lock file, terminating
Sun Sep 23 22:36:20 dbexit:
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to close listening sockets...
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to flush diaglog...
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to close sockets...
Sun Sep 23 22:36:20 [initandlisten] shutdown: waiting for fs preallocator...
Sun Sep 23 22:36:20 [initandlisten] shutdown: closing all files...
Sun Sep 23 22:36:20 [initandlisten] closeAllFiles() finished
Sun Sep 23 22:36:20 dbexit: really exiting now


texts: Find Rows with the Minimum Date per Group


texts:
When designing a view, would it be best to retrieve

the ID fields where FK relationships exist between the joined tables
the desired description field obtained through the FK id relationship
both?

The views I am writing will be primarily used for SSRS reports and I want the views to be adaptable and flexible for multiples uses.

Critiques/advice are encouraged!

Thank you!


texts: choosing between the slaves on streaming replication

texts:
We are building a new streaming replication setup on postgres 9.3 integrated with pgpool for load balancing and failover, and repmgr for replication business. We have seperate daemons running on all postgres nodes and pgpool instances. They basically check status and run remotely called operations (like "go master" or delayed STONITH tasks).

Investigating the scenarios, we have come to a position where we lose the master for good. We need to continue the operation so one of the standbys must be the new master. We are assuming that one of them has the most up-to-date data coming from the master. The question is how to find the most up-to-date node.

Here are some options i can foresee ,

tool like xlogdump for examining wal
methods like Josh Berkus mentioned here http://www.databasesoup.com/2012/10/determining-furthest-ahead-replica.html
rendering the pg_xlogs folder's contents

So how can we find the most up-to-date standby on a master fail?


texts: Can I programmatically generate reports that are emailed as Excel docs from SSRS?

texts:
I've searched already and don't see (or can't decipher) a solution for my issue. I have SSRS emailing a report for 1 district (with 12 retail stores). However, each Region has over 8 Districts, and I need to put each District on its own tab. I have multiple tabs working, but I'd like to avoid creating each sheet if possible. I also need to generate 4 of these master spreadsheets - is there a way to make some sort of template for one worksheet using parameters?
We're using SQL Server 2008 R2 and Report Builder 3.0


texts: Select query not giving result for inner query values

texts:
Here is my schema and data

create table mytable (id numeric, val text);
create table mytable1 (id1 numeric, id text);

insert into mytable values (123, 'aaa');
insert into mytable values (124, 'bbb');
insert into mytable values (125, 'ccc');
insert into mytable1 values (1001, '[123]');
insert into mytable1 values (1002, '[123,124]');
insert into mytable1 values (1003, '[123,124,125]');

When I am running the below query, I am getting expected result.

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003

Result:

123,124,125

But, when I am passing the above query as inner query for a select query, I am not getting the result

select mt.val from mytable mt where mt.id = any (select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003)

Expected result:

val
---
aaa
bbb
ccc

Anything wrong in the query?

(using Postgresql-9.1)


texts: Compound unique constraint with NULL values

texts:
With the following table structure

CREATE TABLE favorite_comments(
   id SERIAL PRIMARY KEY NOT NULL,
   opinion_id BIGINT REFERENCES opinions(id),
   review_id BIGINT REFERENCES reviews(id),
   list_id INTEGER REFERENCES lists(id) NOT NULL
);

This are valid records (Review and opinion related to the same list):

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4

But i would like to prevent that same review or opinion can appear twice related to the same list:

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4
3,         NULL,        444,        4 <- WRONG

I would like to add a constraint that checks a conditional uniqueness, UNIQUE(opinion_id, list_id) OR UNIQUE(review_id, list_id), but since opinion_id,review_id can be NULL im having some trouble with this

Ive tried this but does not work

ALTER TABLE favorite_comments
ADD CONSTRAINT comments_can_only_be_saved_once_per_list
UNIQUE (opinion_id, review_id, list_id);


texts: Restoring database on shared remote NAS folder

texts:
We are trying to restore a 2TB database on a shared folder on a NAS so we can run several tests on it. So far I understand, this is doable, restoring on a shared folder on a remote NAS. But it fails with the following error.

  The operating system returned the error '1317(The specified account does not exist.)' while attempting 'CreateFileW' on '\xxx.xxx.xxx.xx\myshared_folder\db_file.mdf'.
  Msg 3013, Level 16, State 1, Line 12 RESTORE DATABASE is terminating abnormally.

When we run the restore script, it creates the file, with zero size and then fails.

From all digging and searches, many issues are related to user permissions or incorrect user. We already verified, with our sysadmin help, on the NAS logs that the correct user is connecting to the NAS. Manually we can copy/delete files to that folder, using the required user. We tried changing the IP to the name, error. Also tried mapping the drive, both on explorer and on tsql, to be sure that the session was using the correct place, same error again. Everything we try fails.

Tried following steps provided on this MSDN entry but doesn't work.

Here the OP had same issue, but with SQL2008R2 and didn't get any answer neither. Some of the answers points out that 'Hosting SQL Server database files on a NAS is not supported, and even if it would work, it would end up in extreme slow performance'. But I wouldn't say so.

Here is the code we're using to run the restore, together with the queries to map the NAS folder and another one to test that the drive exists. Of we run locally everything runs smoothly.

USE [master];
RESTORE DATABASE sunhotels_event
 FROM DISK = '\\path\file_1.bak',
      DISK = '\\path\file_2.bak',
      DISK = '\\path\file_3.bak',
      DISK = '\\path\file_4.bak'
 WITH NORECOVERY, NOUNLOAD,
    MOVE 'file_Data' TO N'\\xxx.xxx.xxx.xx\myshared_folder\file.mdf',
    MOVE 'file_Log' TO N'\\xxx.xxx.xxx.xx\myshared_folder\file_1.ldf'
 , STATS = 10;

EXEC sys.xp_fixeddrives 1;
EXEC xp_Cmdshell 'net use Z: \\xxx.xxx.xxx.xx\myshared_folder\';

EDIT:
We tried also this other option using sp_addumpdevice but didn't worked neither.


texts:
My listener in Oracle 11g R2 can not start . When I want to give the status of that, the error in below is shown:

Connecting to <ADDRESS=<PROTOCOL=tcp><HODT=><PORT=1521>>
TNS-12541 : TNS:no listener
 TNS-12560 : TNS:protocol adapter error
  TNS-00511 : NO LISTENER
   64-bit Windowns Eroor : 61: Wnknown error

My tnsnames.ora is :

# tnsnames.ora Network Configuration File: D:\app\SHOJAIE\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SHOJAIE-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

My listener.ora is :

# listener.ora Network Configuration File: D:\app\SHOJAIE\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\SHOJAIE\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\SHOJAIE\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SHOJAIE-PC)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\SHOJAIE

Can any one help me ?


texts: Selecting preferred row in database based on condition

texts:
I'm new to SQL coding and I'm struggling to find a solution to my issue.

I have a database with contractors that have worked at our organisation on and off.

The database contains the contractors employment number and their current/past contracts.

I need to check where the contractor has more than one row, that if there is a contractor_code = '24', to also check if there is a contractor_code = '60'. If true then select the contractor_code = '60 row and data and give the CASE multiple rows count of '1'.

Sample Data:

SQL Code:

SELECT DISTINCT
       contractor_no,
       CASE WHEN contractor_code = '24' AND contractor_code = '60'
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
ORDER BY multiple_roles DESC

The table gives all contractor_no multiple_roles value of NULL.

I can't eliminate the rows based on if their active or finished as I want to count a contractor even if they're finished. Some columns don't have data in date_commenced or date_finished.

Any suggestions would be great, even if I'm way off track with a Case statement.


texts: How to return a set of rows from this function?

texts:
I am new to Postgres functions and have a table with the following structure:

CREATE TABLE options.options (
  delta double precision,
  gamma double precision,
  rho double precision,
  theta double precision,
  impvol double precision,
  value double precision,
  vega double precision,
  id bigserial NOT NULL,
  date bigint,
  ticker text,
  callput text,
  chg double precision,
  maturity integer,
  symbol text,
  strike double precision,
  implied double precision,
  last double precision,
  vol double precision,
  ask double precision,
  bid double precision,
  CONSTRAINT options_pkey PRIMARY KEY (id)
);

And I am trying to build the following function:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS SETOF varchar(250) AS -- declare return type!
$BODY$
BEGIN -- required for plpgsql

   RETURN QUERY
    select maturity,avg(Implied) from options.options
    where ticker=arg_ticker and date=arg_date and strike/arg_underlying>arg_lower
    and strike/arg_underlying<arg_higher group by maturity order by maturity asc;

END; -- required for plpgsql
$BODY$ LANGUAGE plpgsql;

The issue I am having is the following error when trying to run the following query:

Query

select * from generate_term_structure_by_moneyness('TQQQ',20151221,120.2699,.98,1.02)

Error

ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type character varying in column 1.
CONTEXT:  PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY
********** Error **********

ERROR: structure of query does not match function result type
SQL state: 42804
Detail: Returned type integer does not match expected type character varying in column 1.
Context: PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY

I believe the error is coming from returns setof but I am unsure how to cater to this result type.

In pseudo code the returned data is expected in the form of:

[int,float; int, float; ...]


texts:
We have an application that needs access to the database all the time to work properly and we are planning on deploying SQL Server 2012 AlwaysOn Availability Groups on Windows Server 2012.

We have two geographically separated data centers and we plan to keep one DB server in DC1 and the other in DC2.

All the information that I've seen shows a local synchronous copy and an asynchronous copy in another data center. I wanted to know if there is a way to configure the availability group with just two SQL servers which are separated geographically and can support automatic failover.


texts: Populate database column with drop-down menu item

texts:
I'm quite new to Apex and Oracle in general, but I would like to know how to populate a database column with the data from
the drop-down list

Currently I'm not getting further than simply creating the drop-down items

What I'm going for is that if the user chooses (e.g.) Company2 and department7 and inserts data into the tabular and pushes submit, then the Company data would populate the company column in the db;  dep would populate department column and same principle for other drop-down items)


texts:
I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought).
The queries is like this:
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1
WHERE Status in (1, 2)

This seems to work as it is inserting the project after I add it to table1, however everytime I add a new project it seems that the trigger inserts a new row for each project, I have in table1. I would like it to only add the new project.
Is this doable?
Thanks!


texts: Which database is best for deeply embedded database or through C DLL?

texts:
I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are:

At least 10 GB Database
Database encryption and search in encryption
Full text Search
Possible updating of database through Online [This is only additional benifit]
Must have C DLL

There are many options available like MySQL, Oracle, MSSQL. An opensource database would be great.


texts: Modeling of batch handling of other table objects?

texts:
I have a Postgres DB that has two tables, Cleanings and Inspections. They are similar but have their own methods and columns so they are in separate tables. Normally these are created one at a time and their details entered into a form. But I want users to be able to scan in (the items being cleaned or inspected have barcodes) a large batch of items, enter any common data (date of cleaning, who performed cleaning, etc.) into a form, and have the back end build all of the cleaning or inspection records in one batch automatically.
I can make a form that does this, and the back end just runs through the batch processing. But I think there would be some value in recording when batches were cleaned or inspected, and which items were processed in that batch. I'm trying to figure out the best approach. My system is Ruby on Rails, with Postgres DB. The three objects I'm currently working with are:
ppe: items that can be cleaned and inspected.
cleaning: cleaning record of a ppe item, has a ppe_id foreign key.
inspections: inspection record of a ppe item, has a ppe_id foreign key.
My idea: Single Table Inheritance; Using a table called batches that has columns:
| id | type | user_id | ppe_ids | batched_item_ids | notes |

id: primary key.
type: the two models that will inherit from this, cleaning and inspection. Rails recognizes this as the indicator of the class being referred to in the STI.
user_id: the user that created the record.
ppe_ids: an array of ids of the items being cleaning or inspected.
batched_item_ids: an array of ids of the cleaning or inspection records created from the batch.
notes: text field of notes for the batch.
Second idea: Join table, table called batches that has the columns:
| id | user_id | notes |

Same definitions as above. I will then have a table called ppes_batches like so:
ppes_batches
| id | batch_id | ppe_id | type |

id: primary key.
batch_id: foreign key of the batch referenced.
ppe_id: foreign key of the item being referenced.
type: type of record, would be cleaning or inspection. This could also work as STI. Rails has a lot of syntactic sugar from join tables. The main advantage of this is storing id's as arrays in the DB is not always a best practice since it has to be translated in some way by Rails. Postgres has an Array column type and is recognized by Rails but I always like to consider DB agnostic solutions.
Third idea: Join tables with no STI. So the same batches table as the previous example, but two join tables:
cleanings_batches
| id | batch_id | cleaning_id |

inspections_batches
| id | batch_id | inspection_id |
These are just a primary key and two foreign keys. Rails associations allows me to take a Batch object and get all of the items in the batch through:
some_batch = Batch.first
 #returns the first batch object in the DB
some_batch.cleanings
 #returns all of the cleaning objects associated with that batch through the join table
some_batch.cleanings.joins(:ppes)
 #returns a join of the cleaning objects and their associated ppe

The main reason to even record this info is because a user might want to recall a list of items that were sent together. Other than that I don't want to spend a huge amount of effort on this, but I know if I don't address it, clients will probably request it. LOL


texts:
Based on this question I understand the meaning of the following sorts of errors:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

However I'm seeing:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.266

I find a fractional transaction count to be equal parts funny, scary, and odd. I'm sorry I don't have a simple script to share that would reproduce.

Can anyone explain this behavior?


texts:
On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that says "User deleted", then upon further inspection I see that the operation failed with an "Unknown error". The instance's error logs show the following error:
06:42:36.347 UTC [199]: [1-1] db=cloudsqladmin,user=cloudsqlagent ERROR:  role "user1" cannot be dropped because some objects depend on it
06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL:  privileges for database mydb
24 objects in database mydb

I am able to see the 24 objects by running
SELECT distinct table_name
FROM information_schema.role_table_grants
WHERE grantee = 'user1'

Following the advice at https://cloud.google.com/sql/docs/postgres/create-manage-users#deleting_users and I know that I must reassign the ownership to another role (postgres) before dropping the user, but when I attempt to run the following script (from https://stackoverflow.com/questions/51256454/cannot-drop-postgresql-role-error-cannot-be-dropped-because-some-objects-depe):
REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;
DROP USER user1;

I am presented with a permissions error on the REASSIGN command (ERROR:  permission denied to reassign objects) due to the fact that Cloud SQL doesn't give my user SUPERUSER status (https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions). I am also unsure of how to grant SUPERUSER status to a user in a Cloud SQL database instance; my assumption is that I cannot.
This issue is also occurring when using the gcloud command of the Cloud Shell. The output is similar:
$ gcloud sql users delete user1 --instance=<INSTANCE_ID_REDACTED>
user1@None will be deleted. New connections can no longer be made
using this user. Existing connections are not affected.

Do you want to continue (Y/n)? Y

Deleting Cloud SQL user...failed.
ERROR: (gcloud.sql.users.delete) [INTERNAL_ERROR]

Important information:

Postgres Version 9.6
A read replica is attached to this instance
Yes, I have tried turning it off and on again. No, the restart of both the main instance and the read replica did not solve this issue.
All database schema migrations and data migrations on this instance have been accomplished using Flask-Alembic with a very standard configuration
I can verify that no users have altered the schema outside of the Alembic process in our Cloud Build configuration
The user was created in the GCP console
No manual alterations have been made to the user, privileges, or object ownership


texts:
I have a table as this example:

CREATE TABLE customerEvent(
    customerID  int NOT NULL,
    eventID     int NOT NULL
)

customerID may appear in many records. I use the following query to identify most frequent customerID:

SELECT
    customerID,eventID
    ,row_number() OVER(PARTITION BY customerID ORDER BY eventID) as frequency
FROM customerEvent
ORDER BY frequency DESC

With this I see those most frequent customerID. But they appear only once on the top of the list. Example:

customerID  eventID frequency
7           3       5
15          9       4
7           1       4
9           4       3
7           10      3
15          2       3

I need all records of the most frequent customerID to appear on the top, example:

customerID  eventID frequency
7           3       5
7           1       4
7           10      3
15          9       4
15          2       3
9           4       3

Any idea how I can build a query that does that?


texts:
the Business Intelligence guy is asking for faster machines, latest software etc. and some of his customers are complaining of query times in excess of an hour.
The tool is Cognos/Insight and the DB is replicated copy of a master MySQL which we are shortly to migrate to MariaDB.

My gut feel ( before I get down and dirty with the SQL and Execution plans ) is that we are simply dealing with poorly optimised querys coming out of Cognos and an inappropriate collection of indexes to support them.

My Question is ( before I bespoke a solution ) :
Are there accepted strategies for realtime slave tables that allow for indexes that are not present on the master table?


texts: Why performance of this query deteriorates over time?

texts:
I have a 1 million row table in oracle 19c. I am running this query. Table has an index on ID column, stats are up to date.
select <columns>
from (select <columns> from <tableA> where ID > 42000 order by ID ASC)
where rownum < 1001;

Next one will be
select <columns>
from (select <columns> from <tableA> where ID > 43000 order by ID ASC)
where rownum < 1001;

This is not efficient, the ID is different each time and the query has to loop 1000 times to cover 1 Million rows in the table. Initially for few minutes the query is super fast, several milliseconds, then   it starts to slow down and the last few iterations take over 30 seconds per execution.
How can I rewrite or speed it up ? I gathered stats, increased SGA, shared pool size etc.


texts: Dimension for Product Properties based on Product Type - Sparse Dimension?

texts:
I am creating dimension of product properties for sales facts.

Property of product depend of product type. For example:
- Type = smartphone. Properties = model, OS, size
- Type = book. Properties = author, title

How dimension should be for this case?

Should I create dimension which contain ALL properties?
In this case dimension content will be sparse, there will be many null values.

|----------------------------------------------------|
| DimKey | Type | Model | OS | Size | AUTHOR | TITLE |

OR, should I create dimension for each?
In this case sales fact will have many FKs.

|-------------------------------------------------------------|
| FactKey | Quantity | Total | Book_FK | Smartphone_FK | .... |

Is there any other way to do this?


texts: Testing Geometry Polygon Data is Valid Using a Stored Procedure

texts:
We are using a SQL Server Spatial database with the following table setup:

SRID of 27700
geometry_column of ogr_geometry
geometry_type of POLYGON

I am using a SQL Server stored procedure to perform the following test:

geometry::STGeomFromText(@poly, @srid).STIsValid();

When tested the using the following it correctly returns true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 50, "POLYGON((1 1, 3 3, 3 1, 1 1))")
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 0)

When I run the live data (held in the recordset rs) it always returns false even though I suspect about half the polygons should be true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 1000000000, rs("ogr_geometry"))
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 27700)

Am I doing anything that is obviously wrong or could it be the case that although some of the geometry works in our system none of it is strictly valid?

I have prepared a ZIP file with an example polygon that works and one that does not. Both are being flagged by SQL Server as invalid.


texts: Extended Events: Specify Session Data Storage left blank

texts: What happens if the Data Storage is never configured?


texts: Index options and applying partitioning to an index

texts:
I have following two questions:

I have a SQL Server index with the following options:

PAD_INDEX = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
DROP_EXISTING = OFF
ONLINE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON
FILLFACTOR = 70

Where are these details are stored in the database? I have found tables like sys.indexes and sys.stats where a few of these options are stored.  I need the complete list, if possible.
To partition an index in SQL Server, we need to drop it and recreate it by associating it with partition scheme.  Is this the only way to apply partition on an existing index?


texts: What are the best practices for ETL of data from one OLTP system to another?

texts:
First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

I also started a similar topic that I labelled "What are the best practices for ETL of data from one OLTP system to another Part 2?"


texts:
I have this issue. I have three types of posts a user can generate (some users can generate all some can only generate one type).  I have the following types: main, employee and admin posts.

I expect to have to deal with millions of posts (all three types).  But about 90% of all posts would be main_posts. Also, most reads from the DB would be of the main_posts too.

NOTE
The main_posts table would have about 50-100 more fields than what is currently there. update
those fields would be normalized into different tables. The ideea is that the main-posts table would have the bulk of the posts and therefore would have the most reads and writes.

Which design option is more sound?

Option1

or Option 2

Thank you.


texts:
I used to work with MySQL/PhpMyAdmin for web application and portable SQLCE for light local program. I just started with SQL Server Express 2016 and SSMS and just cannot understand Transaction Log. I have read various posts including MSDN: The Transaction Log and A beginner’s guide to SQL Server transaction logs, but I still cannot conceptually understanding even one single need for Transaction Log (the *.ldf file) and my lack of understanding leads me to think the log file is just a complete waste of disk space.

In MSDN it states the transaction log supports the following operations:

  Individual transaction recovery.

So it logs all my query and data? Then what is the point of having the
*.mdf data file in the first place? Alternatively, if I back up my data and database schema, I can always recreate my database and insert back my data, and I have no need for my *.ldf

  Recovery of all incomplete transactions when SQL Server is started.

“Incomplete Transaction" - does that mean if there is a power outage before a process is completed, the log file will resume/repair the transaction automatically when the power is back on and server is running?

  Rolling a restored database, file, filegroup, or page forward to the
  point of failure.

"Rolling"... does that mean there are subversions of database within the log file like a backup machine?

  Supporting transactional replication.

I found no explanation. Does it mean I can repeatedly insert duplicate rows of the same data? It make no sense.

  Supporting high availability and disaster recovery solutions: Always
  On Availability Groups, database mirroring, and log shipping.

So..if my *.mdf database file got corrupted, the log file will mirror and recreate the whole thing uncorrupted?

At last, the log file seems contain everything there is in a *.mdf data file plus a good number of functions wrapping it. I thought either the *.mdf file is not needed, or alternatively if my schema and data is regularly backup, then there is no need for log file. But I am sure my understanding is incorrect.


texts: Is it bad practice to use separate columns for parts of date values?

texts:
I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit:  My use case is write once, read lots, and the date values in rows will never change.


texts:
I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a series of binary executables, because apparently I've been a bad person and need to be punished. So, I don't have access to the queries in order to port them.

That's fine, I thought. I can run the query profiler and log all SELECT statements that occur during the nightly report generation.

Except it turns out that the original developer shaved the cost down by running SQL Server Workgroup Edition, which doesn't include Profiler.

I was considering using triggers to get these queries, but I think I can't use triggers on SELECT statements. Reverse engineering the queries is possible, but given the complexity of the database (multiple, often recursive, relationships between tables, loads of similarly-named tables where several haven't been updated in years, and so on) I would prefer to have canonical queries to reference.

How should I go about this? Is there any way to get Profiler running in Workgroup Edition? Is there a way to enable query logging without the profiler? I'm not worried about performance because this app doesn't get particularly heavy use and it's running on a machine that's two sizes beefier than the app requires.


texts: Recover original utf8 data from latin1 mysql db

texts:
We forgot to change charsets in our mysql db after migration on new server.

Current situation:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

But table charset is utf-8:

mysql> show create table inbox\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `inbox` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  # ...
  `message` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4001 DEFAULT CHARSET=utf8

We're using Node.js (0.10.*) for backend and latest felixge node-mysql (connection is UTF8_GENERAL_CI by default).

Before we've found incorrect config, there are a lot of messages saved to table (~4000), most of them are utf-8. Sure, currently we see a lot of question marks ?????????? instead of normal text.

I've tried node-iconv, but no luck.

var db = require('mysql').createConnection({
  // credentials
});

var iconv = new require('iconv').Iconv('latin1', 'utf-8');

db.query('select message from inbox where id = 42', function(err, result) {
  var msg = result[0].message;
  console.log(iconv.convert(msg).toString());
  db.destroy();
});

Is there any chance to recover original utf-8 data?


texts: SQL Server 2005 Restore Backup Status Recovery Pending

texts:
So at work something dreadful happened a database server went down and I am having an issue recovering one of our databases from our backups.  When I try to restore from a backup using SQL Management Studio the backup proceeds to 100% then fails at the end with the following:

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8

Error Number: 5243
Severity: 22
State: 1
Line Number: 1

There were some articles stating that a hotfix or servicepack update solves issues similar to this but it did not in my case.  From what I've read, error 5243 and 5242 usually mean possible corruption.  I ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'
WITH CHECKSUM;

and it indicated that no checksums were in use so I then ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'

and it completed successfully with:

Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
Directory lookup for the file "M:\Data\DataBaseName.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "N:\Logs\DataBaseName_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.

Sense no checksums are used this only checks the header, this I know.  Now I try to force the restore to complete using CONTINUE_AFTER_ERROR:

RESTORE DATABASE DataBaseName
FROM DISK=N'C:\DataBaseName.bak'
WITH CONTINUE_AFTER_ERROR;

This completes successfully with:

Processed 1453208 pages for database 'DataBaseName', file 'DEFAULTDB' on file 1.
Processed 3 pages for database 'DataBaseName', file 'DEFAULTDB_log' on file 1.
Restore was successful but deferred transactions remain. These transactions can not be resolved because there are data that is unavailable. Either use RESTORE to make that data available or drop the filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 1453211 pages in 457.277 seconds (26.033 MB/sec).

Here is where things get frustrating :) At this point the status of the database is "Recovery Pending".  I have checked this by using this command:

SELECT
    [state_desc]
FROM
    sys.databases
WHERE
    [name] = N'DataBaseName';

I can take the database off line using

ALTER DATABASE DataBaseName SET OFFLINE;

and the status shows offline. I then try bringing it back online with

ALTER DATABASE DataBaseName SET ONLINE;

and it fails with:

Msg 945, Level 14, State 2, Line 1
Database 'DataBaseName' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5243, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8.

but when I check the state it is in "Pending Restore"

I then issue

ALTER DATABASE DataBaseName SET SINGLE_USER;

and this completes sucessfully.  I then issue

ALTER DATABASE DataBaseName SET EMERGENCY;

and this completes sucessfully...good right? I then try

USE DataBaseName

and recieve, Msg 922, Level 14, State 1, Line 1
Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I am pretty much stuck here.  If I run

DBCC CHECKDB (DataBaseTable, REPAIR_ALLOW_DATA_LOSS)

and still recieve:

Msg 922, Level 14, State 1, Line 1
Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I can't seem to get any further than this.  Can anyone give me their two cents?  Thanks a ton! I definitely know I am going to learn something here.


texts: Upgrade lock from mongo 2.4 to 2.6 wont go away

texts:
I have upgrade 3 months ago a mongoDB sharded cluster from 2.4.9 to 2.6.1 . I haven't faced any issues with the upgrade and my cluster working fine, but i still see the initial lock that required to update metadata to v5 on my config servers.

     "_id" : "configUpgrade",
    "process" : "pgdb2:27017:1399361525:1804289383",
    "state" : 0,
    "ts" : ObjectId("53688ff569fb6f34e9a2d398"),
    "when" : ISODate("2014-05-06T07:32:05.343Z"),
    "who" : "pgdb2:27017:1399361525:1804289383:mongosMain:846930886",
    "why" : "upgrading config database to new format v5"

Does anybody knows if this is bug or if it is going to affect any future operation?


texts: How are the DBA, SYSDBA, SYSOPER roles and SYS, SYSTEM users related in Oracle?

texts:
I don't know if I understand correctly the privileges of these built-in users and roles.
This is the list of information I have found:

I think that DBA and SYSDBA roles enable to do everything - if this is true, then why are there two names for the same thing?
the SYSOPER role enables to do everything except of accessing users' data,
the SYS user has DBA and SYSDBA roles, so he can do anything,
the SYSTEM user has DBA role, so it seems that he could do anything, but I have read that he cannot do backups, recovery, and DB upgrade.

Do I understand it correctly?


texts:
For example (just to illustrate the problem),

CREATE TABLE test_join(ID INT NOT NULL);
--1
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1;
--2
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1
INNER JOIN test_join c ON 1=1;

The first one gives expected "ORA-00918: column ambiguously defined". The second one executes without any complaints (Oracle 10g).
I wonder if it's a bug or I'm missing something obvious in name resolution rules. Oracle 11 gives error in both cases.

Thank you


texts: How many files are created by default using WiredTiger storage engine?

texts:
So, I got a question on a test (without answer unfortunately) that left me with a big doubt whether it's true or false.

The question is this: "By default, WiredTiger storage engine creates one file per collection and per index".

I went to check all the documentation, but I couldn't find a really satisfying answer to this question. I would assume by logic that it of course creates one file per collection and one per index, but how to be sure about that? I think those files are managed by the DB system and thus are not visible to the user.

I just remember that somewhere there are the files of the database looking like dbname.0 and when it grows after a certain threshold, it creates another file dbname.1 etc. so I would assume the collection is inside that file and not a separate entity, thus making the test statement wrong?


texts: Delete back ups more than 2 weeks in sql server

texts:
I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario?

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASE',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 336

This below script is my regular backup.

declare @Date varchar(16),
        @query nvarchar(100);

select @Date = convert(varchar,Year(getdate())) + right('00' +  convert(varchar,Month(getdate())),2) + right('00' + convert(varchar,Day(getdate())),2) + right('00'+convert(varchar,datepart(hh,getdate())),2) + right('00'+convert(varchar,datepart(Mi,getdate())),2);

select @query = N'BACKUP DATABASE db_CP_Staging TO  DISK = ''D:\backups\db_C_Staging\db_C_Staging' + @Date + '.bak' + '''';

exec sp_executesql @statement = @query;

How can I add cleanup time to this query? Can some one help me on this?


texts: How to use distinct and non distanct coloumn type in same mysql query

texts:
My brain has stuck in this query, I want to know that how to get distinct and non distnct query result in one query. Here is my query:
SELECT count(PTPK) as count,PTPK, Name ,`SwitchDate` ,Half ,
    status,ProjStatus , PM_Name as ProModeDetail, Employee_Name as Supervisor
            FROM
                (SELECT distinct PTPK, `SwitchDate` ,Half, LeadBy
                  FROM `switch_person`
                  where `SwitchDate` between '2020-01-01'
                                         and '2020-01-03'
                    and `Half` between 1 and 2)
                as a

            LEFT JOIN
            (
            select Pk, Name, status, StatusMode from projects
            ) as b
            on a.ptpk = b.pk

            LEFT JOIN
            (
            select pk, status as ProjStatus from project_status
            ) as c
            on b.status = c.pk

            LEFT JOIN
            (
            select PM_PK, PM_Name, ProjectStatusId from project_mode
            ) as d
            on b.StatusMode = d.PM_PK

            LEFT JOIN
            (
            select PK, Employee_Name from employee
            ) as e
            on e.PK = a.LeadBy

            WHERE PTPK <> 0
            GROUP BY PTPK

Now at this part:
(SELECT distinct PTPK, `SwitchDate` ,Half, LeadBy
       FROM `switch_person`
       where `SwitchDate` between '2020-01-01'
         and '2020-01-03'
         and `Half` between 1 and 2)

You can see i am getting distinct PTPK, I want also non-distniact data in another column. How do i acheive this?


texts: How can I set Timeout by View, User or Role?

texts:
For SQL 2008 R2, data views.

Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query.

I have been unable to find timeout controls by View, User or Role using SSMS.

There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits).

I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this https://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client"

Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application.

I have control over what is in the Data View Query, and any SQL settings that do not impact the main application.
I have control over the User Permissions that can access  the data view
I do not have control over external report application(s)
Snapshot isolation is not an option (application considerations)
The hints NOLOCK & READPAST are not viable solutions in SQL 2008 R2 https://stackoverflow.com/questions/15770402/sql-2008-nolock-vs-readpast-considerations-for-reporting-accuracy


texts: Nonclustered Index Rebuild Online on Heap Table

texts:
Recently read that rebuilding a heap online in SQL Server Enterprise is single threaded and does not go parallel.
My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?
Background
I am trying to find the best place to actually test it, but right now I have limited hardware available to me.  I am new to the company and I am not advocating a heap, I am trying to steer them towards clustered indexes, but they have random GUIDs everywhere and lots of the developers think clustered indexes will hurt performance.  I am trying to educate them on the advantages of using clustered indexes rather than heaps and one of those components is index maintenance.
We had one stored procedure that was doing over 27 million logical reads.  I went in and looked at fragmentation on one of the key table scans, rebuilt the heap and logical reads went down to 560,000.  Just looking at forwarded records and fragmentation I would not have even rebuilt that table through a scripted job.  Trying to make them understand if we are gonna keep heaps we need to perform regular maintenance on these things.
The cost of this maintenance is higher for rebuilding heaps than having clustered indexes.  That is really where my question about single threaded is coming from.  Sorry to be long winded but thought would help explain question.


texts: Need Suggesion on PostgreSQL Database Minor upgrade Procedure from 9.5.15 to 9.5.19:

texts:
Thanks Alot each and every one.

Could you please help us to below points:

We are trying to deploy the latest postgres minor version 9.5.19.

We have deployed postgres server using yum repository.

Existing Version: 9.5.15
OS Platform : RHEL 7.2

target Verion: 9.5.19

What are the pre-requisities.
How  to Deploy the latest minor release.
What are the Post-Deployment activities.
What are the Rollback activities

Please share some best practices for this activity..


texts:
A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over 100 MB in size, however it actually exceeds 7.5 GB.

What can cause this? I suspect that I can make the problem go away by adding a clustered index or recreating the table completely, but I want to understand what's going on that makes one staging table inflate to such ridiculous proportions. Here's what I've gathered as pertinent data so far:

sys.dm_db_index_physical_stats (DETAILED)
    fragment_count = 1056
    page_count = 964392
    forwarded_record_count = 0

sp_spaceused @objname = 'db.schema.table',@updateusage = 'TRUE'
    reserved = 7716888 KB
    data = 7715136 KB
    index_size = 24 KB
    unused = 1728 KB

sys.allocation_units
    total_pages = 964611
    used_pages = 964395
    data_pages = 964392

Can anyone tell me why this table is occupying so much space?


texts: How can I publish a SOAP Web Service that takes data from an Oracle 12c Database?

texts: I have an Oracle 12c Database. I want to extract data from this database and publish them via SOAP web services. I know to do this with REST web services (via ORDS and APEX utilities) but I find it a little tricky with SOAP since I couldn't find helpful information on the web.


texts:
I have setup a transactional replication on a sql 2008 database.
Then I backup the database.I got following error when I try to restore the database.
'Exclusive access could not be obtained because the database is in use'

Do I have to remove the replication before restore database?


texts: case statement using characters for sum value

texts:
I have a table that I am trying to pull a single return value on a select saying that either compressions on table is enabled or disabled by using a sum calculator for column value compressed must be No and auto_compressed must equal Disabled.

Here is example data in the table:

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname
collate          TBLSpace
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Enabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       Yes
auto_compressed  Disabled

You can use the below to test:

create table test (
partnum         varchar(255),
dbsname         varchar(255),
owner           varchar(255),
tabname         varchar(255),
collate         varchar(255),
dbsnum          varchar(255),
compressed      varchar(255),
auto_compressed varchar(255));

insert into test values ('1048577','rootdbs','informix','','TBLSpace','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Enabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','Yes','Disabled');

I only want to determine at the end of the day if compressed = No or auto_compressed = Disabled for all records in the database then I want a single return saying disabled or enabled.

I put this together, but I am struggling to replace numbers with values:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 'Enabled' else 'Disabled' end) as Condition
from test

I get this error message below:

367: Sums and averages cannot be computed for character columns.

I can replace the values with numbers and it works like below:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 1 else 0 end) as seven
from test

I believe my case statement is incorrect or there is a better way of doing this. I do need assistance as my mind is boggling at this point.


texts: Allowing users access to SQLPLUS and to compile PLSQL

texts:
As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data has undergone ETL. The tables contain a large numbers of rows but not necessarily large rows in size. The analysis - will be - I assume of calculus in nature. The users are wanting to run their own code on the data for the purposes of ad-hoc analysis - within their own schemas. They come from a SAS background which from my understanding has allowed them to write and run their own analysis of data. Albeit, SAS runs on its own dataset - extracted from Oracle or elsewhere.

In my opinion this is a dangerous set-up - but without answering my own question - what are the pitfalls?


texts: Optimal way(performance wise) to store array like data in MySQL

texts:
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:

order(
item1(item id, quantity)
item2(item id, quantity))

And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,

The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if  the user submits an order with * number of items i will have * number of rows for the order in the order items table.

When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.


texts: Can import some tablets from mysql 8.0.19 into a 5.7 database?

texts:
I'm in the process to transfer a wordpress site to a new server which uses an older version of mysql 5.7.23, so I want to start with a clean install of WordPress and database, so only the necessary tables will be brought to the new database, but the old server is using mysql 8.0.19.

So the question is: Can I import tables from mysql 8 to a database using an older mysql (5.7)?
If so, do I need to do anything beside importing the dump?


texts:
How to make notification when some conditions written by user are true.

I have one table DATA which has 3 rows.

name  value
----  -----
a1    10
a2    20
a3    30

This table changes some values every 1-10 minutes.

Example: a1=12 , a2=21

ALERT:
if a1>11 and a2>20 send me some message.

Question: How to check when a value in DATA fulfils the conditions in alert, and then my php/python environment takes any action on this alarm?

My idea:

After update on DATA table run trigger that will check if ALERT exist and set active on it (how to check when DATA id a1=12 and then check if there is some alert like a1>11)
Run every 1 minute process that checks if ALERT is active and execute it.

I don't now where to start...

I will use: PostgreSQL, php, cron